Combining Two Data Sheets

I have worked this out on my own via these forums but the current method I use is a huge convoluted mess of VBA and I know there has to be a more efficient way (using VBA / VLOOKUP).

Basically I a system that keeps a customer list - I export it to excel and it comes with no formatting etc. I format it and I have added some columns on the end with extra data.

Now, once a day or so I run a new report..the issue is that some of the data changes (sales figures, dates etc). I need to combine my old and new lists keeping formatting, using all the new data but keeping 3 of my columns that I added in the old sheet.

Yes it's confusing to explain.

If you check my example spreadsheet maybe it will make more sense.

Sheet1 is my working list I have added formatting and added columsn O-S
Sheet2 is the newly exported list and how it looks. So what I need to do is VLOOKUP columns O-R from Sheet1 and add to Sheet2 and then copy the formatting from Sheet 1 to Sheet 2. Basically this combines the two lists keeping columns O-S (or O-R actually..S is just a forumla to parse column K) but taking the new vlues for all the other formulas (from Sheet2)

Does this even make sense??!?

Sheet1 is the finished product and what I work with every day.
Sheet2 is basically the same thing in raw form when I first get my reports.

The only difference is in Sheet1 I have added columsn O-S...those are things I add in as I contact my customers. Also I have added color formatting in to the whole table (if you look you can see green and red and various colors)

All I need is Sheet2 to VLOOKUP values fro Sheet1 Columns O-R and then take the formatting from sheet1. Does that make more sense?

I can do this I just thought it might be too messy.
If I do it myself it will basically be lots of copy pasting data around and so messy.

IE My macro loads the new file, pastes in a new sheet, adds the new columns needed O-S - adds the vlookup formulas to those columns. Not sure how to set the formatting though..

Maybe this is way too hard to explain and maybe I should just use the same method method I used before with lots of macros and vba lol

I had to strip my real file down because of file restriction. My actual file has a ton of modules and sheets.

I see where your confusion is, I think I accidentally deleted some columns in sheet1. They're supposed to match, I had several columns hidden.

Basically sheet1 and sheet2 are the same. Let's just say I started from scratch today. I went in my work system and I exported my customer list - it would look just like what you see on Sheet2.

Then what I did was add columns O-S, they are simply some columns that I can label events on those accounts. Column S is just a parsed email from column K. As the week progresses I add more notes into O-S.
At some point I need to export a new list from my work system because I get new customers daily, and new orders and the list needs to be up to date.

So then I need to paste the NEW (sheet2) over the old (sheet1) retaining all the NEW information from the most recent file, but I need to KEEP the added information in Columns O-S.

I don't know how to explain it any better

To clear confusion add a new A and B column in Sheet1 labeled Customer and WedID so that it will match sheet2.

I can figure this out I just know there is probably a better way. The way I will do it involves lots of copy and pasting and blah.

I'm going to mark this one as solved as I think I am sorting it out and don't want time wasted on it. I have new related questions will post new threads

Originally Posted by xladept

OK - maybe later!

Thanks for the rep!

Thanks for the help - I did post a different thread. I sorted out some other things and clarified my question in the other post better. It's still similar to this post but not the same so I thought a new thread was better - hehe Thanks again!

I am dumb, I am using VLOOKUPS on this form...lol.......... so I just need to add them lol. I thought we had switched to the non VLOOKUP form on this one but that was the other form you helped me with..my bad.