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?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
Platform
  1. Windows
there are all five csv files so all data and it was done by Power Query

Okay I've never used Power Query but I figured out how to open it and load the five CSVs. What did you do after that or is there a tutorial on what to do in a case like this?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
General tut is here with almost everything about PQ
 

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

General tut is here with almost everything about PQ
Okay. Did you have to use the advanced editor? I sure appreciate that you were able to create the kind of output I need but I still have 100,000 rows to transform. :) I want to learn how to do it myself.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
Did you have to use the advanced editor?
Why? No, everything from the PQE (Power Query Editor) ribbon
if you click step by step in Applied steps window you will see what I did
for each csv file the steps are the same
first you need to clean csv files, no additional spaces, the same format . Each csv table should look the same (structure)
for the Append1 steps is less steps because most of the necessary steps were done before with csv files

100000 rows is not too much :)
 

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Why? No, everything from the PQE (Power Query Editor) ribbon
if you click step by step in Applied steps window you will see what I did
for each csv file the steps are the same
first you need to clean csv files, no additional spaces, the same format . Each csv table should look the same (structure)
for the Append1 steps is less steps because most of the necessary steps were done before with csv files

100000 rows is not too much :)

Thanks for your patience. This is what I see when I open the ribbon and load the file that you uploaded. What do I do next to "click step by step in Applied"?
 

sandy666

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

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
Platform
  1. Windows
See, I do not have all of those steps when I load the xls file provided here. I guess the file that you uploaded did not save the steps?
 

Attachments

  • Capture.JPG
    Capture.JPG
    216.6 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,129,983
Messages
5,639,380
Members
417,084
Latest member
elitepaper

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