Excel Masters Please Help

[aclark]

I have two excel files that I'd like to merge as easily as possible rather than going row by row. The data on one is sales YTD and the data on the other is sales on a Rolling 12 month. I have some items that have been sold to an account on a R12, that haven't been sold YTD so my rows don't line up perfectly. I am hoping there's a way for Excel to figure this out, and match the first column up to fill in the data. I'll try to show below....

File 1				File 2
Account 1	Item 1	5		Account 1	Item 1	8
Account 1	Item 2	3		Account 1	Item 3	5
Account 1	Item 3	4		Account 2	Item 1	1
Account 2	Item 1	8

How I'd like the final file to look

FINAL FILE
Account 1	Item 1	5	8
Account 1	Item 2	3
Account 1	Item 3	4	5
Account 2	Item 1	8	1

 

[maansmit]

I have two excel files that I'd like to merge as easily as possible rather than going row by row. The data on one is sales YTD and the data on the other is sales on a Rolling 12 month. I have some items that have been sold to an account on a R12, that haven't been sold YTD so my rows don't line up perfectly. I am hoping there's a way for Excel to figure this out, and match the first column up to fill in the data. I'll try to show below....

File 1				File 2
Account 1	Item 1	5		Account 1	Item 1	8
Account 1	Item 2	3		Account 1	Item 3	5
Account 1	Item 3	4		Account 2	Item 1	1
Account 2	Item 1	8

How I'd like the final file to look

FINAL FILE
Account 1	Item 1	5	8
Account 1	Item 2	3
Account 1	Item 3	4	5
Account 2	Item 1	8	1

Should be able to use a vlookup to pull the data from file #2 into file #1.

Feel free to shoot me a PM if you have questions.

 

[russc2542]

Yes, there are several ways it could be done. user actions, formulas, crazy formulas, vba scripting. which works best depends on how big the X and Y dimensions are.

Also open to PMs.

 

[HHollow]

Past the file 2 data at the end of file 1. Then use Data/Sort to put the rows in oder of account.

 

[searpinski]

Did you solve this? If not, please post where you're at.

 

[aclark]

Did you solve this? If not, please post where you're at.

I did it completely manually this time. It will be an ongoing report, so I'd like to find a quick and effective way to do it. Going to look though some options.

 

[aclark]

Yes, there are several ways it could be done. user actions, formulas, crazy formulas, vba scripting. which works best depends on how big the X and Y dimensions are.

Also open to PMs.

Both files have 3 columns. I run the report for multiple accounts, the longest having about 320 rows. Not very big files, but will eventually grow over time. I can upload example files if that would help.

File 1
Account	Cases YTD TY	Cases YTD LY
File 2
Account	Cases R12 TY	Cases R12 LY

Final File
Account	Cases YTD TY	Cases YTD LY	Cases R12 TY	Cases R12 LY

 

[searpinski]

Are you looking to do it once (combine the files for good) or more of an active thing where your one master file can combine the two other files as needed?

 

[aclark]

Are you looking to do it once (combine the files for good) or more of an active thing where your one master file can combine the two other files as needed?

The final file will be new every 3 months. I will rerun both reports to create a new file.

 

[poptab]

SELECT file_1.account AS account, 
       file_1.item AS item, 
       file_1.value AS value_1,
       file_2.value AS value_2 FROM file_1
    LEFT JOIN file_2 AS file_2 ON (file_1.account = file_2.account AND file_1.item = file_2.item)
    ORDER BY file_1.account

edit: add order by