Any excel wizards out there?

[Calvinsmam]

I’m being driven insane with this task and I think there must be an easier way to do it!

Please oh wise ones, please tell me how to do this.

Basically I have two spreadsheets

One has 3000 items on it and information about the items.
Size, weight, colour

The other is 700 of the items but also has a key piece of information about the items that needs to go into the other spreadsheet (date of delivery) but no other information.

I need to make a spreadsheet that has the item, weight, size, colour but and delivery date of the 700 items.

I can’t cope and paste it in a block because the two spreadsheets don’t tally in the same order as they have different numbers of items.

Is there a way to do this without having to manually go through????

I’m going mad. If you can’t tell me how to do it send wine.

I think you should be able to use the VLOOKUP function. In your 700 item table, create a column which uses the VLOOKUP to search through the longer table, match the item name, then extract the colour value. You then drag the formula down so it does this for each of the 700 items
Then repeat for the other values you want.

I can help with the syntax if you want but that will have to wait until.tomorrlw.morning!

My sympathies!

Can you sort the data on both spreadsheets so they are then in the same order?

They are both in alphabetical order but because one spreadsheet has more items in the other then they don’t match up.

Thanks loads lemon but I don’t know what that means I’ve never really used spreadsheets except for budgeting. It’s not something I normally do.

You can totally use VLOOKUP. I can help you tomorrow! Send me a PM and I'll send you the formulas you need

Actually bothered to login to reply (at last a thread I can answer!) and say Vlookup but Lemon beat me to it

Thank you so much lemon!!!

The inportant thing, is there a unique identifier or key for each row in the spreadsheets? And is this key the same for the rows where you want to match in both spreadsheets?

If yes, then as said before you can use VLOOKUP. if not, you will have difficulty.

Hi silver
Yes the item name is the same on each spreadsheet.

I’ve looked into vlookup and it appears to be very complicated!

Vlookups look horrendous and I’d been scared of them for years (tried to use the wizard and it never worked) but hubby told me to ignore all that and showed me and its acrually pretty easy.

@Calvinsmam are the names written down identically in both spreadsheets? If so, vlookup will be easy, I promise! I’m also happy to help if you want to drop me a PM

Yes the names are identical in both.

At least they should be

I use vlookup all the time. It looks horribly complicated, and the online help isn't aimed at idiots like me. Once you have mastered it, it is easy. I am going out now, but if you haven't got the answers by the time I get back I will send you a step by step guide with screenshots.

Thank you all so much, I could honestly cry you’re being so nice.

Vlook up

Use the function wizard its the little 'fx' icon next to the white bar that you can type into.

Make sure that column A on both lists is the name, put both lists into the same spreadsheet workbook, on different tabs.

For the formula click on Fx icon, type vlookup into the search and click on it.

Look up value is the 'Name' thats the same on both sheets so just clickon the call.A1

Table array - highlight all of the otger spradsheet that you want to take info from

Column index is the column that the information you want is in, so if date is in column C, its the 3rd along and you type 3

Range looku p - type the word 'false' that means that the 2 lists are not in the same order

hope this helps.

Once you've done it for 1 item you can just copy and paste it down the whole list

Wow spelling was award ful in my response. Sorry, on a kindle and its a bugger for typing