Quickbooks Export to Excel Summary

[mvasquez]

Hello all!

New user to the forums here - looking to learn a lot more about excel and it's advanced features.

My current quest is to provide a useful excel sheet about products we purchase. In Quickbooks, I can export purchases we made by all vendors for all parts, and if we bought at different times, different prices, etc.

The problem is the export has summary rows in between all the data. What I need to do is create a sheet that has each part # that exists, and the highest and lowest price we ever bought it for.

So in the screenshot, we have 00397-03 many times at different price points. I want to get the highest and lowest price out of this section, and these "mini-tables" exist all throughout the file.. so I don't know how best to grab the data.

Is this a pipe dream or is there a way to build this file from the data I have? I have attached a few screenshots but I blurred some of the data for protection of our internal stuff.

Thanks in advance.
Marc

 

[AlanSidman]

Pictures
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.

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

 

[mvasquez]

Fair enough. Thanks for the clear request. I'll put all this together and post back shortly. Thank you!!

 

[mvasquez]

Attached are 2 files. QBExported Data is the result file that Quickbooks provides, with it's merged cells and mess (part of why this is so hard). I removed all data I didn't want to show, and changed vendor names so it is as accurate to the real file as possible.

ResultFile is what data I want out of the QBExported per each grouping of a part #. Sometimes there are only one of the part, so the high and low will be the same. Other times there are multiple different points of purchase.

For this sample file, I manually calculated the points of data, but need to find a way to do this in bulk as there are thousands of lines on the actual report.

I know merged cells are terrible, but that's how the report comes.

Hope this helps convey what I am trying to do.
Thanks,

Marc

 

[Peter Bartholomew]

That must be the most unhelpful bit of formatting QuickBooks could have thought of; it couldn't be much worse in Paint!

I would have thought that Power Query is your best hope. You can remove records that contain the word 'Total', fill down the headings, remove blank columns, filter out null records. All somewhat painful the first time through but if you get it right the second time is simply a refresh.

 

[bosco_yip]

Formula solution but with combined 2 workbooks into 1 and together with helper column.

1] In "Sheet1" helper AA2, copied down :

=IF(G2="Bill",LEFT(LOOKUP("zzz",C$1:C2),FIND(" ",LOOKUP("zzz",C$1:C2)&" ")-1),"")

2] In "result" A3, copied down :

=IFERROR(INDEX(Sheet1!$AA$2:$AA$500,MATCH(0,INDEX(COUNTIF(A$2:A2,Sheet1!$AA$2:$AA$500&""),0),0)),"")

3] In "result" B3, copied down :

=IF($A3="","",AGGREGATE(14,6,Sheet1!$U$1:$U$500/(Sheet1!$AA$1:$AA$500=$A3),1))

4] In "result" C3, copied down :

=IF($A3="","",AGGREGATE(15,6,Sheet1!$U$1:$U$500/(Sheet1!$AA$1:$AA$500=$A3),1))

5] In "result" D3, copied down :

=IF($A3="","",IFERROR(AVERAGEIF(Sheet1!$AA:$AA,$A3,Sheet1!$U:$U),""))

6] In "result" E3, copied down :

=IF($A3="","",COUNTIFS(Sheet1!$U:$U,"<>",Sheet1!$AA:$AA,$A3))

Regards
Bosco

 

[vletm]

mvasquez
If You'll have more rows to summary then You would check this sample too.
Press [ Do It ] and get results in 'Sheet2'.

Question: How did You get Your Sample Results?

 

[AlanSidman]

I gave this a try with Power Query. Some of my numbers are off a bit from yours. I did not recalculate yours manually to determine the difference. But it seems to do what you need. Attached is the file with the Query shown.

Here is the MCode which you can see in the Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Type", type text}, {"Column6", type any}, {"Date", type datetime}, {"Column7", type any}, {"Num", Int64.Type}, {"Column8", type any}, {"Memo", type any}, {"Column9", type any}, {"Source Name", type text}, {"Column10", type any}, {"Qty", Int64.Type}, {"Column11", type any}, {"U/M", type any}, {"Column12", type any}, {"Cost Price", type number}, {"Column13", type any}, {"Amount", type number}, {"Column14", type any}, {"Balance", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column4", "Column5", "Type", "Column6", "Date", "Column7", "Num", "Column8", "Memo", "Column9", "Source Name", "Column10", "Column11", "U/M", "Column12", "Column13", "Column14", "Column1"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column3"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([Qty] <> null) and ([Column2] = "00397-03" or [Column2] = "00397-04 (00397-04 | 1/4"" Brass Bulkhead Coupling (1.50"" long))" or [Column2] = "00397-06 (00397-06 | 3/8"" Brass Bulkhead Coupling (1.50"" long))" or [Column2] = "00397-08 (00397-08 | 1/2"" Brass Bulkhead Coupling (1.50"" long))")),
    #"Split Column by Position" = Table.SplitColumn(#"Filtered Rows1", "Column2", Splitter.SplitTextByPositions({0, 8}, false), {"Column2.1", "Column2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column2.1", type text}, {"Column2.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Column2.2"})
in
    #"Removed Columns2"