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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,190
Office Version
  1. 365
Platform
  1. Windows
How about some sample data that we can manipulate. Use XL2BB or post to third party location
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
something like this with all years and customers?
(this is a part of the whole table only)
customerAttribute201820172016201520142013
A.J.fpuga0.592252187
A.J.fpugm0.17405573
A.J.knetprofit0.52806720.8730526320.265130594
A.J.krtdte0.6913297660.9961694160.614779124
A.J.pcmp0.5660553120.4160113190.162406332
A.J.pnetprofit0.7902876130.9571583340.408600093
A.J.pntwe0.8192837880.6839030870.755086352
A.J.posipu0.365696814
A.J.posk0.3691159110.3981877250.177100008
A.J.posp0.8132147020.1731779550.961460759
A.J.pqty0.8775870860.2728319020.509159208
A.J.prsatyds0.8881360260.1497658880.451321763
A.J.prtdopps0.7180844810.6653240970.027963905
A.J.prwe0.5448755120.3162065440.625206445
A.J.ptdte0.6000302830.6011573890.036009244
A.J.ptpuyds0.816088369
A.J.pupata0.200137826
A.J.pupatm0.45716236
A.J.pupt0.75041804
A.J.qtyk0.3964607280.2530920310.023308999
A.J.qtyr0.8747542471.1499027180.4719882120.4117407950.748653542
A.J.recsatyds0.1078020511.3277782070.0071553770.0429702090.012260349
A.J.rectdopps0.7102724571.8394961990.1330555170.3250385930.211008432
A.J.recwe0.6947084491.0849826690.535904820.8590763770.577116673
A.J.rnetprofit0.0569345621.6808703590.9029293240.9403909780.616986793
A.J.rpos0.7367839330.7703356020.0907402680.3422776790.383575028
A.J.rseg0.6029110281.4210606550.2778324080.4001269970.656817055
A.J.rtdte0.1229537580.5951505260.9687418390.7599840350.490250829
A.J.segk0.2477707510.7412781980.784728287
A.J.segp0.3472418320.8616616740.748566193
A.J.segpu0.079107872
Aaronqtyr0.7853586820.450409595
Aaronrecsatyds0.181385370.77242115
Aaronrectdopps0.0675639740.686528424
Aaronrecwe0.9535138750.156734509
Aaronrnetprofit0.3935261350.120645165
Aaronrpos0.1112561840.093293536
Aaronrseg0.6895184730.304676596
Aaronrtdte0.6877089010.804575113
AbrahamPosd0.37680352
Abrahamdint0.815831344
Abrahamdnetprofit0.852250692
Abrahamffwe0.685579935
Abrahamfrecwe0.963939056
Abrahamitdwe0.014894218
Abrahampddwe0.465075359
Abrahamsegd0.988283973
Abrahamskdwe0.373301173
Abrahamtadwe0.469808099
Abrahamtfldwe0.85139392
Abrahamtsdwe0.93162591
Abrahamttdwe0.60369716
Adamfpuga0.888340340.4746904650.350611180.010475921
Adamfpugm0.7888488440.9881937610.0085074570.220956174
Adamposipu0.3731902070.8458179350.3783036670.776285648
Adamptpuyds0.374554270.9811901310.7780921270.038152795
Adampupata0.5226189660.5457671880.0488565210.946334251
Adampupatm0.6427465450.5797868460.3469980360.162199508
Adampupt0.9528631190.6932853370.4365678830.718412763
Adamqtyr0.356456327
Adamrecsatyds0.285751933
Adamrectdopps0.67328859
Adamrecwe0.710637512
Adamrnetprofit0.326730297
Adamrpos0.865474389
Adamrseg0.902865201
Adamrtdte0.317692643
Adamsegpu0.7412404280.0098968190.1582495260.911438803
AdriennePosd0.673214953
Adriennedint0.337802272
 

sandy666

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

ADVERTISEMENT

or like this
(this is a part of the whole table (359 records) )
customer201820172016201520142013
A.J.3.90712048517.688336517.7814242967.0511447883.696668701
Andre21.7761678410.1784236
Andrew9.24949197210.295549284.2577709988.1759415474.5919767078.220918327
Anthony5.80112957417.610924657.4233644139.2802879112.96660746514.1429328
Ardarius7.6902106888.6952604192.932084826
Aub4.4800196017.64471029
B.J.7.44023995
Benton3.7803149794.0564480498.721642291
Bill3.798178195
Bre'11.627208225.732247033
Brett2.4315632044.362519089
Brian26.3297598.7568123863.6102246799.656455979
Bruce10.16944513
Bryan8.9093666687.7687458374.876967764
Bryce5.8630383465.975612414
Bud7.794288472
Calvin5.0608888887.3905314192.9037850795.678560341
Chase4.662565195
Chris6.9115655966.7265851037.1685516092.5224895184.510216105
Christion9.9995636147.505871481
Craig6.1874195494.51522091
Cyrus7.2658461889.4935197288.34067888
Dakota8.5657600353.843690225
Damien3.0710192210.611986593.8617383848.089786164
Darius6.7565071410.093519827.342327682
Darrin6.474751112
Deandrew7.5080823727.624115965
Dee13.59873204
Deltron7.1894268529.031859454
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
sorry, I made mistake in previous posts so ignore posts #4 and #5
here is proper (I hope) result
customer201320142015201620172018
A.J.2.9695391254.3930765314.383840375
Andre3.7840930818.708609273
Andrew3.9556593424.4601790333.720617846
Anthony5.4771620685.473754738
Ardarius4.7307716713.485638222
Aub4.085479487
B.J.3.430484325
Benton4.551309614
Bill3.798178195
Bre'4.774233996
Brett2.431563204
Brian5.0198854264.8893487294.734792169
Bruce4.761774175
Bryan4.8769677643.7832657424.309966557
Bryce5.863038346
Bud4.490765017
Calvin3.728680819
Chase4.662565195
Chris2.841610435
Christion4.1239504145.185098743
Craig4.51522091
Cyrus3.9610347653.425310611
Dakota4.922988147
Damien3.908449244.204108602
Darius4.801859607
Darrin2.617935673
Deandrew3.2892935954.217275502
Dee4.63748934
Deltron4.1679687193.578327602
Demetrius5.047286979
Denzel3.135299083
Derek3.989988733
Derrick4.853619812
Devin3.4251780594.011605816
Devon5.795599514
Donte4.294694976
Drew4.5705045353.559559165
Dylan4.608635125
Eddie4.246338156
Fransohn4.928011432
Garrett4.0948178382.0940528364.0358681827.3263967467.375972754
Gehrig2.817147435
Gerrit3.936989842
Greg4.11713579
Hakeem4.057324338
Hale4.1457726383.611885167
Harrison4.664953721
Hayden4.026848684
Henry3.72152292
Imani4.1345999153.6809529195.307589194
Irv3.560876382
Jacob3.345235012
Jalen3.872752975
Jamarcus3.9437379043.360937391
Jamari3.780711299
Jaylen4.752902322
Jeremiah4.144851258
Jimmy4.900976732
Jon4.700436216
Jonathan3.496960994
Joseph2.7799950765.168815168
Josh3.651717597
Joshua3.6884827623.1504363523.630045302
Kailon2.2148827343.504364534
Keith3.558795626
Kendall3.758765721
Kennard3.453702718
Kenny4.453016044
Kenyan2.2133846762.924004393
Kobie3.291700364
Kwadarrius4.9884599
L.T.4.4437704514.271165991
LaVonne3.921876047
Lamarcus4.8173104965.472683588
Landon4.325070084
Larry3.455654336
Malachi5.319136855
Malik4.141738503
Manny3.1382890245.16222858
Matt4.035478414
Maudrecus3.032565216
Maurice2.038269004
Maverick2.888210928
Michael4.973062797
Mikee3.5973781464.530106379
Miller3.797229043
Minkah5.121164072.75224715
Mykel4.3741900354.313309512
Nate3.8240883834.806774073
Noland4.120153069
O.J.3.139730377
Paco4.261627851
Richard4.58687766
Riley3.440687973
Rodarius3.710387081
Rona2.7057175053.3381104063.25685981
Ronnie5.27016135
Sam3.484365926
Santonio4.856669258
Shawn3.3114750063.646491606
Shayne2.376549801
Starling3.901377705
Timothy3.541927033.5761930574.074315876
Trevon3.8126612434.888921563
Tristan5.680589018
Ty3.859302433
Tyler3.4703333284.085051884
Tyrell4.4933814533.486600038
Tyren4.461127624
Van5.0454347783.9759834164.3543516374.299331885
Weston3.408753077
Xavian4.4117847293.902007281
Xzavier2.6761172533.623650562
Zach4.4371621494.163459825
 

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
57
Office Version
  1. 365
Platform
  1. Windows
something like this with all years and customers?
(this is a part of the whole table only)
customerAttribute201820172016201520142013
A.J.fpuga0.592252187
A.J.fpugm0.17405573
A.J.knetprofit0.52806720.8730526320.265130594
A.J.krtdte0.6913297660.9961694160.614779124
A.J.pcmp0.5660553120.4160113190.162406332
A.J.pnetprofit0.7902876130.9571583340.408600093
A.J.pntwe0.8192837880.6839030870.755086352
A.J.posipu0.365696814
A.J.posk0.3691159110.3981877250.177100008
A.J.posp0.8132147020.1731779550.961460759
A.J.pqty0.8775870860.2728319020.509159208
A.J.prsatyds0.8881360260.1497658880.451321763
A.J.prtdopps0.7180844810.6653240970.027963905
A.J.prwe0.5448755120.3162065440.625206445
A.J.ptdte0.6000302830.6011573890.036009244
A.J.ptpuyds0.816088369
A.J.pupata0.200137826
A.J.pupatm0.45716236
A.J.pupt0.75041804
A.J.qtyk0.3964607280.2530920310.023308999
A.J.qtyr0.8747542471.1499027180.4719882120.4117407950.748653542
A.J.recsatyds0.1078020511.3277782070.0071553770.0429702090.012260349
A.J.rectdopps0.7102724571.8394961990.1330555170.3250385930.211008432
A.J.recwe0.6947084491.0849826690.535904820.8590763770.577116673
A.J.rnetprofit0.0569345621.6808703590.9029293240.9403909780.616986793
A.J.rpos0.7367839330.7703356020.0907402680.3422776790.383575028
A.J.rseg0.6029110281.4210606550.2778324080.4001269970.656817055
A.J.rtdte0.1229537580.5951505260.9687418390.7599840350.490250829
A.J.segk0.2477707510.7412781980.784728287
A.J.segp0.3472418320.8616616740.748566193
A.J.segpu0.079107872
Aaronqtyr0.7853586820.450409595
Aaronrecsatyds0.181385370.77242115
Aaronrectdopps0.0675639740.686528424
Aaronrecwe0.9535138750.156734509
Aaronrnetprofit0.3935261350.120645165
Aaronrpos0.1112561840.093293536
Aaronrseg0.6895184730.304676596
Aaronrtdte0.6877089010.804575113
AbrahamPosd0.37680352
Abrahamdint0.815831344
Abrahamdnetprofit0.852250692
Abrahamffwe0.685579935
Abrahamfrecwe0.963939056
Abrahamitdwe0.014894218
Abrahampddwe0.465075359
Abrahamsegd0.988283973
Abrahamskdwe0.373301173
Abrahamtadwe0.469808099
Abrahamtfldwe0.85139392
Abrahamtsdwe0.93162591
Abrahamttdwe0.60369716
Adamfpuga0.888340340.4746904650.350611180.010475921
Adamfpugm0.7888488440.9881937610.0085074570.220956174
Adamposipu0.3731902070.8458179350.3783036670.776285648
Adamptpuyds0.374554270.9811901310.7780921270.038152795
Adampupata0.5226189660.5457671880.0488565210.946334251
Adampupatm0.6427465450.5797868460.3469980360.162199508
Adampupt0.9528631190.6932853370.4365678830.718412763
Adamqtyr0.356456327
Adamrecsatyds0.285751933
Adamrectdopps0.67328859
Adamrecwe0.710637512
Adamrnetprofit0.326730297
Adamrpos0.865474389
Adamrseg0.902865201
Adamrtdte0.317692643
Adamsegpu0.7412404280.0098968190.1582495260.911438803
AdriennePosd0.673214953
Adriennedint0.337802272

Here is an example of the output that I am looking for. I did 2013 Lacoste manually.

 

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?

Having done 2013 Lacoste manually, the ID column was just an Access automatically generated value and should be ignored for every table.

These columns exist in every table:


companyidincpostyear1companycustomeridcustomer

So they would not need to be duplicated in the output.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,677
Messages
5,637,735
Members
416,981
Latest member
PLonchar

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