SOLVED

Combining data

%3CLINGO-SUB%20id%3D%22lingo-sub-2505078%22%20slang%3D%22en-US%22%3ECombining%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2505078%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20I'm%20running%20Windows%2010%20and%20Excel%202007.%20I%20have%20a%20spreadsheet%20exported%20from%20QuickBooks%20and%20want%20to%20better%20show%20the%20data.%20There%20are%20a%20few%20bits%20of%20information%20I%20want%20to%20see%20and%20don't%20really%20know%20where%20to%20start.%20I%20will%20attach%20a%20clip%20of%20it%20to%20show%20an%20example.%20I%20can%20attach%20the%20complete%20exported%20file%20if%20need%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PaulMin_0-1625103120352.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292795iCDDEDDDA34C698C9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PaulMin_0-1625103120352.png%22%20alt%3D%22PaulMin_0-1625103120352.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20As%20you%20can%20hopefully%20see%20I%20have%20highlighted%20a%20section.%20This%20column%20are%20invoice%20numbers%20and%20the%20export%20breaks%20it%20into%20line%20items%20per%20invoice.%20I%20want%20a%20row%20to%20show%20the%20culmination%20of%20the%20line%20items%20along%20with%20the%20original%20amount%2C%20paid%20amount.%20What%20is%20being%20shown%20to%20me%20as%20is%20is%20a%20line%20item%20with%20an%20original%20amount%20and%20if%20that%20amount%20is%20more%20than%20the%20Paid%20amount%20then%20there%20is%20a%20deposit%20on%20the%20account.%20I%20want%20to%20be%20able%20to%20pick%20out%20the%20deposits%20and%20show%20them%20separately%20so%20I%20know%20the%20bottom%20line%20does%20not%20include%20these%20deposits.%20It%20is%20confusing%20to%20me%20when%20I%20read%20this%20report%20as%20it%20is.%20What%20I%20want%20to%20do%20if%20possible%20is%20make%20some%20sort%20of%20temple%20that%20I%20can%20run%20this%20exported%20report%20thru%20to%20show%20these%20things.%20The%20report%20is%20just%20a%20year%20to%20date%20transaction%20report.%3C%2FP%3E%3CP%3E%26nbsp%3B%20I%20know%20a%20little%20about%20Excel%20but%20am%20lost%20here.%20I%20am%20not%20even%20sure%20this%20is%20possible.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20Any%20input%20would%20be%20welcomed.%3C%2FP%3E%3CP%3E%26nbsp%3B%20Thank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20Paul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2505078%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513882%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513882%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1092605%22%20target%3D%22_blank%22%3E%40PaulMin%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20share%20a%20sample%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2514181%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2514181%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1092605%22%20target%3D%22_blank%22%3E%40PaulMin%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20converted%20your%20table%20into%20an%20excel%20formatted%20table%2C%20added%20it%20to%20a%20data%20model%20and%20created%20a%20pivot%20table.%3C%2FP%3E%3CP%3Eyou%20can%20use%20the%20slicers%20to%20display%20by%20Month%20or%20by%20Date%20on%20the%20top%20right%20of%20each%20slicer%20there%20is%20a%20funnel%2C%20you%20can%20click%20that%20to%20remove%20the%20filters%20and%20show%20all.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1625398510254.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293387i3B8B9B903BDF240E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1625398510254.png%22%20alt%3D%22Yea_So_0-1625398510254.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

  I'm running Windows 10 and Excel 2007. I have a spreadsheet exported from QuickBooks and want to better show the data. There are a few bits of information I want to see and don't really know where to start. I will attach a clip of it to show an example. I can attach the complete exported file if need be.

 

PaulMin_0-1625103120352.png

  As you can hopefully see I have highlighted a section. This column are invoice numbers and the export breaks it into line items per invoice. I want a row to show the culmination of the line items along with the original amount, paid amount. What is being shown to me as is is a line item with an original amount and if that amount is more than the Paid amount then there is a deposit on the account. I want to be able to pick out the deposits and show them separately so I know the bottom line does not include these deposits. It is confusing to me when I read this report as it is. What I want to do if possible is make some sort of temple that I can run this exported report thru to show these things. The report is just a year to date transaction report.

  I know a little about Excel but am lost here. I am not even sure this is possible. 

  Any input would be welcomed.

  Thank you,

  Paul

5 Replies

Hi @PaulMin,

 

Can you share a sample file?

 

cheers

@Yea_So ,

  Here is a file I pulled last week. I removed names etc. but the data is there.

 

best response confirmed by PaulMin (New Contributor)
Solution

Hi @PaulMin,

 

I converted your table into an excel formatted table, added it to a data model and created a pivot table.

you can use the slicers to display by Month or by Date on the top right of each slicer there is a funnel, you can click that to remove the filters and show all.

Yea_So_0-1625398510254.png

 

cheers

 

Yea_So,
That was fast. Thank you so much. I have to open it in Open Office as it looks better there. I have Excel 2007 and that doesn't allow slicers. But at least I can see this is possible. There are a few more tweaks but I will see if I can handle them. Very much appreciated.

Hi @PaulMin,

 

You are very welcome. 

 

Just as an FYI Microsoft 365 Family is just 9.99 a month or 99.00 per year so you get most of the bells and whistles in excel for that subscription and knowing you have some type of business you might want to afford it.

Compare All Microsoft 365 Plans (Formerly Office 365) - Microsoft Store

Yea_So_0-1625417536681.png

 

cheers