Posts by fess67

    Re: AUD $100 - Compare columns in two workbooks and remove row if match is negative


    I will answer both posts:


    Timeframe - happy for it to be done in a couple of weeks.


    John:


    1. Yes, always A7. That report is the result of some code I have developed and ran prior to this request.
    2. I have a list of 30 or so cost codes. Some may or may not be present in that weeks output. When I download the original report from the database it arrives as a worksheet named report. I open it, run my code then manually compare it with the cost code report, removing entries that are not present in the cost code report.
    3. Yes. I create a folder for each week ending date.
    4. Yes. Again this is something I go in and do manually at the moment. I think I can work out the code to sum it using xldown.select and a range function but if you can do it then it would be nice :)


    I can show my current code if that will help, although it does not address the issue here.


    Here is my current workflow:


    • Create both reports from the database (I have no control over the formatting and it is terrible)
    • Save cost code as 'full cost code'
    • open each of the invoice reports one at a time
    • run my code to put them into the format shown in the example 2222222
    • manually compare invoice v cost code to remove the old invoices
    • save as the invoice cost code - in this case 222222
    • Once I have done this process with all the workbooks I copy them into a single workbook as separate work sheets and present to the client.


    Thanks for taking the time to respond

    Hi,


    Worksheets 222222 and cost code (attached)


    222222 contains historical data. cost code is current data.


    I need to compare the invoice numbers in col A of 222222 with the invoice numbers in col B of cost code.


    If the invoice does not exist in cost code then remove row from 222222 else leave it there.


    I need to do this over multiple workbooks with different names but 1 at a time. 222222 is simply a sanitised cost code, so the next sheet could be 449281.


    I am happy to name each sheet 'report', execute the code and then change it to the proper name (in this instance 222222)


    Thanks in advance.


    Paypal payment has been made to Ozgrid.
    forum.ozgrid.com/index.php?attachment/64879/
    forum.ozgrid.com/index.php?attachment/64880/

    Re: Match dates - sum totals - merge and centre result


    wow thanks for the response.


    I have spent the time since you posted it working through the code. I see what you did with the nested if - Although I have never quite understood the syntax quite well enough to write them.


    I had to change the code slightly as the subtotal function was adding up the dates rather than the total. Basically copied your for loop and changed the column and offset. I also copied and pasted values from the final merged cells to remove the formula.



    I cannot thank you enough. I have one more issue to solve and I will reduce my processing time from 4 hours to less than an hour. I will post my final request in the Hire forum so I can pay back to the community. Please look in there over the next day or two.


    Again, thank you so much

    Re: How do I go back a step when debugging?


    I think I must be missing something here. I have read the provided link and tried the steps outlined above however I cannot get the changes in the excel sheet to reverse.


    Example: I do a line of code that deletes column G. I realise that this is not what I wanted, I wanted to delete Col F. I cannot go back and have Col G reinstated by going back in the code to before I deleted it. This means that I have to make the correction to my code, close and reopen the spreadsheet, step into the code again and start all over.


    I was hoping there was a way to reverse changes to the sheet - rather like an undo button effect. :)

    Hi,


    I work for a travel agency. I need to run reports that require a summation of values based on date.


    I need to identify common dates (weekending) <col F> then sum the corresponding values from the total column <col E> and present the result as a merged cell <col G>.


    At the moment I do this manually and it is simple to do but given the number of cost codes I have to deal with it takes considerable time. I would naturally prefer to automate the process.


    Attached file shows the original layout on the Original tab and the finished result on the Ozgrid tab.


    All help appreciated.


    Thanks in advance.forum.ozgrid.com/index.php?attachment/64846/

    Re: Compare two columns and return a sum


    hmmm - interesting way to go about it. Certainly it would work for me as I am familiar with Pivot tables but the output is for a corporate client and I am not sure how they will react to it. Worth having the chat though, thanks

    Hi, I come in seek of help again :)


    Our system throws out a delimited report showing the air routes a client uses. We currently edit it manually but it takes time - I am coding excel to help it take seconds :) My code so far has taken the delimited file and put everything in order to be counted, just stuck on the final bit.


    3 columns. From (airport) - To (airport) - #Seats


    I want to count how many times our clients flew from airport A to airport B and then I would like to delete the duplicates to present a report that simply shows the total number each route was flown. Below is a snippet of the list. We can see that the final report would have:


    Adelaide Brisbane 2
    Broome Perth 2
    Brisbane Mt Isa 11


    [TABLE="width: 506"]

    [tr]


    [td][/td]


    [td]

    From

    [/td]


    [td]

    To

    [/td]


    [td]

    Seats

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    ADELAIDE

    [/td]


    [td]

    BRISBANE

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    ADELAIDE

    [/td]


    [td]

    BRISBANE

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BROOME

    [/td]


    [td]

    PERTH

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BROOME

    [/td]


    [td]

    PERTH

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    ADELAIDE

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    BRISBANE

    [/td]


    [td]

    MT ISA

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [/TABLE]


    All ideas welcome.


    Thanks in advance

    At the moment I am making a change to my code. Then I use Ctrl F8 to run to the cursor. I can step through the lines of code to see that everything is doing as I thought.


    However, if a line of code does not behave as expected I am unable to undo that procedure. I have to close the excel sheet (keeping the VBA open) and reopen the sheet. Amend the code and then try again. Pretty frustrating as I am very much a novice in coding and not being able to roll back is very time consuming.


    Any help / tips?


    Thanks

    Re: Identify hidden entries in order to delete rows


    That worked great.


    I had tried an =if(cell="",1,0) and of course I was getting a result that it was not blank. I see your use of the replace function gets rid of that issue. I did not know about .Replace


    If I am reading the MSDN site correctly the (4) in the SpecialCells(4) function targets empty cells. Nice work.


    This should work really well for me, thank you so much. My contact with Excel is so much less these days and the resource and support this site offers is brilliant.


    Thanks again

    Re: Identify hidden entries in order to delete rows


    Quote from Smallman;735674

    What cells do you want to delete? Make it crystal clear so we can identify the logic for the VBA. If you have trouble articulating it, it is even harder at this end.


    Take care


    Smallman


    I think if we look at Col G - any cell that does not have a 'legitimate' entry. Any blank cells in Col G would result in me wanting to delete that row.


    So, in this case, every row except 6,11,15,21,24,27,29,31.


    The intent is to compress the report to eliminate the blank rows.


    Thank you very much for taking the time to look at this.


    Cheers

    Hi - returning user, lost my old username / password. I would say I have a beginner / intermediate knowledge of Excel. I am able to do some formulae and VBA.


    I have an issue that I cannot solve and a search on the web has not helped.


    Problem


    I want to remove all blank lines from a report. I will build a macro or write some VBA to do this. The problem is there are hidden values in all the rows that means I cannot define a logic statement to filter the ones I want to delete. So, how can I identify and delete the content of these cells to allow me to then remove blank rows?


    I have tried ctrl ~ to reveal any code and there is noting in the offending cells.
    I have tried copy and paste values to see if it was an underlying code issue but no joy.
    I have tried copy and paste link and that shows up a number of zeros in the 'empty' cells but not in all.


    I need to effectively identify the blank cells in order to remove them without affecting any of the other information.


    At a bit of a loss.


    You can see from the attached sheet that in col H I have done a countblank and it reports that the cells are not blank. In col I it reports the value is greater than 0. But I cannot see any value in the cells.


    All help / ideas appreciated.
    [ATTACH=CONFIG]63614[/ATTACH]