Closed Thread
Results 1 to 24 of 24

Extracting Data from a table according to specific Criteria?

  1. #1
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Extracting Data from a table according to specific Criteria?

    Hi all,

    Hope someone can help me with this one. I'm using Excel 2007 and I'm looking for a way to extract certain records/rows from a table and to have that data copied to another area of the spreadsheet. I actually want to extract Invoice amounts according to a specific account number so that I eventually end up with all the invoices for a particular client on another tab so that they can be printed out as a kind of client statement.


    I know there are ways to filter and then copy the records/rows to another tab but I want it to be automated as far as possible. What I really want to do is to "pull" the records into a new tab by using some kind of formula in the cells where I want the data rather than having to copy it there. I know how to sum data for this kind of thing but what I'm really struggling on here is being able to "Display all the records in a seperate area" first before I eventually add formulas to sum.

    I don't mind the records being coppied if the process is automated but don't really want to get into macros as writing macros isn't my strong point.

    I've tried using "VLOOKUP" but it will only return values for the first data item it finds (Account number in my case), whereas I really need it to continue to return values for the data item it finds if there are multiple instances of it!

    I'm really starting to pull my hair out now - surely there must be a way???

    Any help will be greatly appreciated

    Thanks in advance

    Dan

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extracting Data from a table according to specific Criteria?

    Depending of your data (an example worth 1000 words) maybe you can use Pivot data...

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Extracting Data from a table according to specific Criteria?

    two methods using functions are possible
    the simplest is to use a helper column on the master sheet the other is an array formula which might make the book slow!
    for helper column method
    say your acount numbers are in col a2 down
    then in another column put =A2&" "&COUNTIF($A$2:A2,A2) and drag down
    youd then get say
    123456 1
    123456 2
    987654 1
    123456 3 and so on you can use that in an index/match on another sheet
    example in another post here
    http://www.excelforum.com/excel-new-...nd-filter.html
    Last edited by martindwilson; 10-24-2009 at 10:27 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Hi again,

    Thanks very much for your quick responses!

    Zbor: I'm not sure about the Pivot Table? I have messed around and couldnt get it to work thought it is probably me. Any examples would help if possible.


    MartinWilson:

    Thanks for that though it sounds a little complicated! I will have a look at your example. Am Busy now bit will prob post again later if I cant solv it


    Thanks again Guys

    Dan

  5. #5
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Hi

    Looked at your example MarinWilson but can't quite see how it is working as yet. It looks very complicated! I really need to understand how it works in order that I can apply it to my own example.

    The helper column? Is this needed in order that any same values (eg account numbers) are made uniquie?

    Anyone else with any ideas please post as while I try to get my head around MartinWilsons' solution I' also open to any other solutions - HELP lol.

    Dan

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extracting Data from a table according to specific Criteria?

    It would be much easier if you upload workbook. Avoid important data but make example workbook as similiar as possible to what you nead.

  7. #7
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Hi

    Ok Zbor I will attempt to upload an example file. Ah yes now I see the Attachments Paper Clip, think I can do it

    Thanks again for your time, I really do appreciate it

    Dan
    Attached Files Attached Files

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Extracting Data from a table according to specific Criteria?

    ok yes thats what helper column does, you can hide it if you dont want to see it
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Thanks Martin I will try to work with that

    Dan

  10. #10
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Heloo again MartinWilson

    Thanks for returning my example file with your solution code.

    I was very excited because it worked just how I wanted it to. But....unfortunately there is a litle problem and it's this:-

    When I enter more date (another invoice to the end of my table, which I have to do from timr to time) the helper colum appears to loose it's count!! Is there any way around this? Ive been desperately trying but as yet cant find a solution. It's really bugging me because your solution is very very good apart from this little glitch.

    Thanks again for your time

    Dan

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extracting Data from a table according to specific Criteria?

    Just extend formula down.

    Anyway, I still think Pivot table can help you:

    invoices%20v2(1).xlsx

  12. #12
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Extracting Data from a table according to specific Criteria?

    =B2&" "&COUNTIF($B$2:B2,B2)
    as long as you drag it down far enough it must count!

  13. #13
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Hi again Zbor,

    Thanks I will have a look at my file that you have returned. I may have overcome the glitch that I mentiond in the above reply but there is another problem anyway: I only want to extract records that are not flagged as "PAID" so that adds more complication to the equation!!

    I do hope a pivot Table can help me so I am now going to check the file

    Thanks again Zbor

    Dan

  14. #14
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Hi again Zbor,

    had a look at the Pivot Table solution that you kindly returned.

    It does work as you said it would but I'm not sure it will work as I need it to. You see I only want to work with one account number at a time and I really need it layed out in straight rows as you saw in my example file. The data Table I work with is as Follows

    Inv no Due Date Acc No Amount VAT Total Status
    105 20/10/2009 ABCDE 100.00 15.00 115.00 PAID
    106 20/10/2009 ABDCF 200.00 30.00 230.00 DUE

    etc.

    And what I need in my result is thae same layout with all the invoices listed that are NOT PAID for the account number I enter into a cell.

    I may be able to use a Pivot Table and then extract the data into the format that I need but it would be a little long winded maybe. Both your solution and Colin's work it's just the final layout of the data that's a bit of an issue. ...And now I have the added problem of only the NONE PAID records being extracted, I think I should have mentioned that earlier but didnt realise it was so significant.

    And after all the trouble you two have gone through in helping me I feel quite bad that I still have'nt completely solved it!?

    It would be great if either of you could help me further but I'll understand if you are are getting a little pissed off with it!

    Thanks again guys

    Dan

  15. #15
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extracting Data from a table according to specific Criteria?

    You can use also report filter in Pivot table. Drag account into that field. Than you can select account and also arrange table as you like.

    And I'm sorry if I didn't put enough effort to solve your problem but the reason is that I never understand quite what you want. So I suggest PT without additional explanation.

    If you know how to use pivot table and it's not good for you then try workout martindwilson approach. If you don't know how to use them, take some time (probably less than hour) and get it how it works so you can adopt it to your problem.

  16. #16
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Thanks Zbor

    It looks like I may have to look at using a PT and adapting it because I cannot adapt ColinWilsons' Helper Colum to count only "DUE" invoices and not the "PAID" ones. I'm sure there IS a way but it's a little beyond me at present.....unless collin can help me??


    all the best

    Dan

  17. #17
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Extracting Data from a table according to specific Criteria?

    colin? who's colin?
    try
    =B2&" "&SUMPRODUCT(($B$2:B2=B2)*($E$2:E2="due"))
    where col e is range with due in
    it will give say
    ABCD 1
    ABCD 1
    ABCD 1
    ABCD 2
    but the match on the other sheet will only find the first one of each and that will be the due one
    having said that as you have 2007 try
    =B2&" "&COUNTIFS($B$2:B2,B2,$E$2:E2,"due")
    Last edited by martindwilson; 10-26-2009 at 12:01 PM.

  18. #18
    Registered User
    Join Date
    08-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extracting Data from a table according to specific Criteria?

    Thanks Martin,

    I used =B2&" "&COUNTIFS($B$2:B2,B2,$E$2:E2,"due") and it worked like a dream!

    Thanks very much for your time Martin that was a great formula!


    Regards

    Dan

  19. #19
    Registered User
    Join Date
    10-26-2009
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Extracting Data from a table according to specific Criteria?

    Hi,

    A completely different approach - see what you think. I only have 2003 so I hope it works on 2007.

    Save the attachment somewhere easily accesible. Then you must open Excel and use File | Open to open the saved file (this ensures that the default Excel directory is set correctly without needing to resort to a macro). You'll see a Query Refresh dialog that you should choose "Enable automatic refresh" on.

    I've made a parameter driven querytable on sheet2 using an Excel ODBC driver. The querytable lists the invoices on sheet1 that match the criteria entered in the yellow cell on sheet2.

    What do you think?
    Attached Files Attached Files

  20. #20
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Extracting Data from a table according to specific Criteria?

    well it works but i have no idea how thats done. care to share?

  21. #21
    Registered User
    Join Date
    10-26-2009
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Extracting Data from a table according to specific Criteria?

    Martin,

    Sorry it has taken me so long to respond - I had DNS problems with www.excelforum.com!

    It is a bit of work to achieve but once it is set up users seem to like it.

    You need to have the data on a sheet on its own (say, Sheet1) in a file (say, Example.xls) that has been saved to the filesystem. Open Example.xls and create / select a blank sheet that you want to have the QueryTable results on. On that sheet type "Account No" in B1 and "abcd" in B2 in preparation for the very first result set. Select cell A4. Select Tools | Macro | Visual Basic Editor and make sure the Immediate window is visible and completely clear of text. Copy the code below into the first line of the Immediate window and press enter. An "Enter Parameter Value" dialog should appear. Select both check boxes and select cell B2 as the source.
    Please Login or Register  to view this content.
    An "Enter Parameter Value" dialog should appear. Select both check boxes and select cell B2 as the source. Close the Visual Basic Editor. If you enter
    "aaba", "abcd" or "acaa" in cell B2 you should see the appropriate results.

    One of the problem issues is the file location. If you hard code the directory path then the file won't work in any other location. A way around this is to enter DBQ=.\Example.xls in the connection string. It means that it will look for the Example.xls in whatever directory is the current directory for Excel (whatever directory shows when you do a File | Open). The current directory can be set manually (by using File | Open to open the file) or by Auto_Open visual basic - I opted for manual here to avoid creating any modules for code.

    It's maybe even more useful if the querytable is in a separate workbook altogether. Then the source data file can be overwritten regularly and the QueryTable returns the updated data...

    I hope you'll be able to follow my ramblings here. Please don't hesitate to ask any further questions.

    Ian

  22. #22
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Extracting Data from a table according to specific Criteria?

    thanx
    ill have a play with it

  23. #23
    Registered User
    Join Date
    09-19-2014
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    1

    Re: Extracting Data from a table according to specific Criteria?

    Hi martindwilson,

    I have attempted to apply the advice you gave to this query. However, I am having a few problems.

    I am unable to pick up the correct information from the exported data tab. In addition I would like the indexing to ignore expenses marked 'excluded'. Would you be able to take a look at this attachment and advise?

    Kind regards,

    Mark

    Expense Report Example.xlsx

  24. #24
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Extracting Data from a table according to specific Criteria?

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1