Sorting info from a query

[Dacomacoop]

I have attached two files to help explain what I am after as I don't even seem smart enough to just describe what I am needing done, LOL. Excel Sheet2 is the data from a query and I want to sort it and combine it in sheet 1 based on "CARD". But the info I need from that card # is on the line above. Is this even possible???? Any help would be greatly appreciated.

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Dacomacoop]

Absolutely, for some reason I never even thought of attaching the excel file.

OK in this file on Sheet2 is where the data is.
Columns A-G are autopopulated from a query, I would like to be able to find the gallons and dollars for each card# and also sort it out based on type of fuel ie Unlead, Dyed and Clear

Does any of this info help or do I need to explain more?

[alansidman]

Attached is your file. I used power query to transform the data such that it was in a format that would allow for analysis. I then brought the power query back to excel and created a pivot table that accumulates your data.

Here is the MCode for Power Query:

Please Login or Register  to view this content.

[rcm]

I wrote a regular macro. The scheme needs more workthough. a clearing of all data (end of month, period, etc)
and all summarized data need to be flagged to avoid repetition on further updates.

[Dacomacoop]

Thank you so very much for all your help

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.