How to combine data from multiple sheets into one?

I have an excel file that contains the outstanding customer accounts for multiple locations, each location on its own sheet. Some customers have outstanding accounts at more than one location. I would like to create a single sheet that combines the customers and their outstanding amounts from these, which I could then update monthly.

For example, if customer John has $100 at location A, $50 at location c, and $200 at location F, on the sheet I want to create, it would show John as having $350 outstanding. How could I do this?

Use PowerQuery (add-in for Ex2010) then PivotTable. I know nothing about your structure of data so I can't say more.

Try

=SUMPRODUCT(SUMIF(INDIRECT("'" & Sheets &"'!A:A"),A2,INDIRECT("'" & Sheets &"'!B:B")))

Sheets is a named range of all your sheet names.

Customers are in column A

Accounts in column B

I threw together an example that should be indicative of what I am working with (can't give actual customer data)

See post #4.

See attached

in B2

=VLOOKUP(A2,INDIRECT("'"&INDEX(Sheets,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheets&"'!$A:$A"),A2)>0),0))&"'!$A:$B"),2,FALSE)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.



in C2

=SUMPRODUCT(SUMIF(INDIRECT("'" & Sheets &"'!A:A"),A2,INDIRECT("'" & Sheets &"'!C:C")))

For Column A, I simple copy/paste for each tab then "remove duplicates"

Originally Posted by JohnTopley

See attached

in B2

=VLOOKUP(A2,INDIRECT("'"&INDEX(Sheets,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheets&"'!$A:$A"),A2)>0),0))&"'!$A:$B"),2,FALSE)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.



in C2

=SUMPRODUCT(SUMIF(INDIRECT("'" & Sheets &"'!A:A"),A2,INDIRECT("'" & Sheets &"'!C:C")))

For Column A, I simple copy/paste for each tab then "remove duplicates"

Thanks for all the replies John. Sorry I didn't get back to you, but I was sick. So this does a great job with the addition of the values, but is there an easier way to get the names besides just copy and pasting and removing the duplicates? We have 11 locations, with hundreds of customers at each location. It would be quite time consuming, and would leave room for error, if I were to have to go through the list each month. Is there a way to automatically have excel handle that?

is that what you want? (no formulas, no vba)
(but it works with PowerQuery installed)

I have a VBA-based solution BUT I would go with Sandy's PowerQuery solution (PQ not installed on my computer!).

Originally Posted by sandy666

is that what you want? (no formulas, no vba)
(but it works with PowerQuery installed)

I appreciate the idea, but I only have excel 2007 to work with, so I don't have power query

Originally Posted by JohnTopley

I have a VBA-based solution BUT I would go with Sandy's PowerQuery solution (PQ not installed on my computer!).

As I don't have power query, I would love to your about your VBA solution.

so why you've 2010 in your profile? for Ex2010 is add-in PowerQuery from MS site

switched jobs, didn't realize I still had that there, oops

so change excel version in your profile for no more mistakes (and waste time )

Please Login or Register  to view this content.

This assumes the summary page is named "Total" as per your posted file. If it something else, change references to Total in the macro.

The VBA collates and sorts unique entries for column A BUT uses the formulae in B2 and C2 (which must not be deleted). The VBA copies the formulae down the columns.

NOTE: it uses ranges not tables.

Click "RUN" button

Originally Posted by JohnTopley

Please Login or Register  to view this content.

This assumes the summary page is named "Total" as per your posted file. If it something else, change references to Total in the macro.

The VBA collates and sorts unique entries for column A BUT uses the formulae in B2 and C2 (which must not be deleted). The VBA copies the formulae down the columns.

NOTE: it uses ranges not tables.

Click "RUN" button

Hi John and Sandy,

I would like to thank you and apologize for being MIA. Please allow me to give you an update.

I tried John's method, and it worked for about half the customers. I tried to figure out what was wrong but couldn't. During this time, I spoke to my managers about getting a new version of Excel, one with Power Query and other features I was missing. They said they would think about it. On the 22nd, I had an idea about what the problem would be, but something came up and I didn't get to post here that Friday. The following week, I had off for Thanksgiving, during which I got sick. I didn't get back to work until this week, and wouldn't you know it, my managers had ordered Office 2016, which got installed today. As I would like to understand both solutions, I am posting now a question to each of you:

John:
It appears that your formula is only searching for 4 cells undersheets. How do I expand this to cover 11 cells so I can search all locations? It appears that it is defined as an array or reference in a way I am not familiar.

Sandy:
I have never used Power Query before. Could you tell me how to use it for this? Could you provide me with some links to learn about it?

Minor change to VBA:

Please Login or Register  to view this content.

and change to named range "Sheets"

=OFFSET(Total!$H$2,,,COUNTIF(Total!$H$2:$H$100,"?*"),1)

btw. don't quote whole post in reply !

Forum rule:

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

check this one

Originally Posted by sandy666

check this one

hi sandy,

that file has everything right. But I tried to follow your steps (and I believe I did) with all the actual data. It was definitely pulling all the sheets together. However, when I get to the part "now select all columns then Remove Duplicates and Sort Ascending if you want" it removes the duplicate names while also removing the duplicate values. Could you maybe elaborate more on what needs to be done after append? Maybe there is something you instinctively know to do

In last step don't remove duplicates but use Group by...
see picture

groupby.jpg

then sort by name if you wish

Originally Posted by sandy666

In last step don't remove duplicates but use Group by...


then sort by name if you wish

Thanks, Sandy! I now have a working solution. Have a great weekend

You are welcome

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

it won't let me give you any more reputation than I already have, sorry. I'm going to wait for John's response before I mark this as solved, just so I know multiple ways of doing this in the future

No problem
You can add rep to John what allow you to add rep to me

There is no need to drag the formulas: the VBA copies the formulas in B2 & C2 to the cells in B and C.

Simply ensure the named range "Sheets" has all the required tabs and then click "RUN".

Please Login or Register  to view this content.

Highlighted code creates "Sheets" list for you

Originally Posted by JohnTopley

Highlighted code creates "Sheets" list for you

I have tried copying your total sheet into my excel file and my other 11 sheets into your excel file. Unfortunately, it gathers all the names and removes duplicates, but it doesn't change the customer number or outstanding. Or rather, it sets all customer numbers to 13296 and sets all outstandings to 850. If I click on a customer number cell, click the formula, and hit CTRL+SHIFT+ENTER then the cell gets the right customer number. If I click on a customer number cell, click the formula, and hit ENTER then the cell gets the outstanding.

What's going wrong? Does it matter that one location has alpha numeric customer numbers?

It always works for me: alpha numeric customer numbers makes no difference.

I think the problem is "Calculation Options" (in "Formulas") is set to "Manual": set to "Automatic".

You were right, that wasn't enabled.

You know what, that's the darndest thing. I know I had already set that and disabled editing directly in the cells. For some reason, editing directly in the cells was still disabled bout this wasn't set to automatic. Gremlins in officde 2016?

Anyway, thanks again for all your help!

Originally Posted by kb9omaaj

Hi Sandy, thanks for the introduction to Power Query. This last part is where I run into trouble. It doesn't add the duplicate values left. So if a customer has amounts at two or three locations, it only displays the value from one location and removes the other value(s). I need each customer's total amount from all locations. Is there a way to do this Power Query.



Hi John,

so I repeated what I did before using the data from your new post, however I have problems. When I run the macro, it find customers and puts them in the sheet without a problem. However, it adds at the top of the list (cell A2) a customer "...", which is not in any location's list. Moreover, it doesn't populate columns B or C. What am I don't wrong? With regards to the =offset... formula that you gave me, where should I enter this? Upon copying your sheet into my file, the sheet range now is H1:H100, but I would like to know how to do this in the future.

EDIT:
Hey John, so upon closing and reopening Excel, the macro works without putting in "..." in A2. Additionally, the formulas in column B and C work. However, they only work if I click on each one, click the formula, and then CTRL+SHIFT+ENTER for column B and ENTER for column C. I can't drag the formulas down or double click the lower right corner, or I just get the value from the cell I started. Any ideas?

Hi Sandy, I hope you're doing well. The file had been working great, however, we are adding a new location and now there are problems. Here is what I am doing:

1. I select the locations tab
2. I go to the Data ribbon and select "From Table" which is in the get and transform group.
3. The query editor opens, I change the name to the location, and click "Close & Load"
4. The excel file works for a while, but then I get an error that "Initialization of the data source failed."
5. If I click ok (the only other option other than to click x on the error box), I can see that the data stops at line 35 (but there should be over 100). I have triedd this repeatedly and it is always in the middle of this line.

Am I forgetting something? What could the error be?