Tried to do this in Access and it didn't work out. Maybe Excel can?

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
84
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have five tables: car sales, motorcycle sales, jet ski sales, trailer sales, and boat sales.
Each table has different columns but they have some column elements: customer ID #, company name, customer ID #, customer name, year.
Each row has the entire year's purchases by a customer but not every customer has a purchase row on each table.

I'd like to have 1 table that has 1 row per customer per year with all of their purchases, regardless of car, boat, jet ski, trailer and motorcycle.

How can I make that table? If the same customers were on each sheet and each of them had 1 row in each sheet, it would be a simple sort. But it won't work with this.

Any ideas?
 
see this
it should work on every Windows version because this is WMV (windows media video)

do the same for each csv file then use Advanced Append feature
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
see this
it should work on every Windows version because this is WMV (windows media video)

do the same for each csv file then use Advanced Append feature

AH, thank you. That was very easy to follow. So once all of the five tables have been transformed as such, what did you do after that?
 
Upvote 0
you need to learn PQE ribbon "map"
I can see how to append the queries in a new sheet. Once I learn this I won't have to ask again. I've never used PQE before and frankly the MS tutorial does not walk me through what you just did, about un-pivoting columns. It doesn't go over a specific use case like this. Can you bring me to the end with this? Again, I do appreciate the patience, really. I don't want people to do things for me if I can learn them myself. I will be able to teach my entire team how to do this with their sales data and it will be important to my org. Can you please bring me in from here?
 
Upvote 0
appendsteps.png
 
Upvote 0
Solution

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top