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

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
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?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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?
 

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

appendsteps.png
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,796
Messages
5,638,385
Members
417,025
Latest member
MusterDuster

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
Top