boring Excel help question

It's a bit tricky to explain what the problem is which is why I haven't been able to find anything by googling.

I have a data set with a column of ID numbers, around 120,000 of them, with most of the ID numbers duplicated about 11 times. I have a separate database (the annotation file) of what these IDs correspond to, with a column for the IDs and a columns of what it represents.

What I need is a column in the data set, next to the ID number column, which contains the info about what each ID represents. Now the IDs in the annotation file are listed in the same order as they are in the data set, but without the duplicates, so that list is only about 12,000 long, and so I can't just copy it across.

Is there any way of doing this or am I wasting my time trying? This doesn't seem like it should be such a hard problem to solve but maybe it's beyond the scope of Excel. If anyone knows anything about anything have a punt!

Sounds like the simplest way would be to delete the duplicates and then copy-paste the second document in, right?

I just did it, but I'm not sure how. Here goes;

Select the column entries top to bottom.

Data > Filter > Auto Filter (click)

Data > Filter > Advanced Filter

Select 'filter in list' and 'unique records only'. Okay

Done.

I think that works as long as your duplicates are displaced randomly about the list. I'm not sure it'll work the way you want if they're in order.

I might be able to make that work, although I don't want to delete the duplicates (but filtering should only hide the duplicates?). I'm not seeing this autofilter option in my version of Excel but I'll try it out, thanks

You may not have to auto-filter if you complete the list range yourself.

For example: R1C1:R7C1

I filtered for unique records, but when you paste a column next to that it pastes as if it isn't filtered and so it doesn't line up properly

LOL, fuck. Not a clue then.

Edit: Although, I wonder what would happen if you pasted the columns beside one another, and then did the duplicate sorting thing. If there are no duplicates in the second column it theoretically shouldn't be a problem.

Edit: I'm not sure if I really trust my method though. I'm just filling out random numbers and testing it myself, but I'm not sure I'd want to use this process for something real. With so much information it might be impossible to know for sure whether everything is in the right place.

Yeah thats basically the problem with so many numbers, you can't check that it's done it right so you have to really have faith in what you're doing... easier said than done with fucking Excel. Thanks a lot for having a go though, I thought you might have cracked it for a bit there!

You'll need a vlookup, so that your new column has the following function:

If your ID is in cell A2, and your annotation table is in Sheet2 A1 to B12000 (and has two columns, IDnumber in the first and text in the second)

=VLOOKUP(A2,Sheet2!$A$1:$B$12000,2)

That should do the trick, just drag the equation down to the bottom and the A2 should change to A3, A4 etc. The '2' in the formula tells it to return the value in the second column of the annotation table.

Also the $'s just fix the reference to the annotation table, so that when you drag the equation down it doesn't automatically come down.

This VLOOKUP command looks like exactly what I want Jhonny, nice work! I'm getting a REF error at the moment but hopefully I can sort that out.

  On 6/28/2011 at 5:32 PM, Coalbucket PI said:

This VLOOKUP command looks like exactly what I want Jhonny, nice work! I'm getting a REF error at the moment but hopefully I can sort that out.

Just do a bit of playing. Your ID is numeric and in order yeah? It should work then

  On 6/28/2011 at 5:46 PM, jhonny said:

  On 6/28/2011 at 5:32 PM, Coalbucket PI said:

This VLOOKUP command looks like exactly what I want Jhonny, nice work! I'm getting a REF error at the moment but hopefully I can sort that out.

Just do a bit of playing. Your ID is numeric and in order yeah? It should work then

They are in order but not entirely numeric, I think that might be the problem now. Most of them are 7 digits followed by '_at'.

edit: maybe I want the LOOKUP one?

Yes I think I got it now with the LOOKUP formula. Almost the same as the VLOOKUP one you gave me but it works with non-numeric I guess. Thanks!

nice one, glad you sorted it.

Bob Odenkirk: "Great Job"