Stupid Excel question...
September 15, 2017 11:12 AM Subscribe
Hi. I'm not sure how to Google this one. Let's say that I have three Excel sheets, each one being a dictionary of a different language, each sheet being about 1500 words apiece.
For each entry in each dictionary, each word is pegged to a unique number, so that theoretically you're matching the Ruritanian word for "butter" with the Freedonian word for "butter", except instead of synchronizing everything to the English word "butter", you've assigned "butter" to the Filing Number 123456. If a language has no word for butter, then that number might be skipped. If that language has a hard distinction between two different kinds of butter, then you might have two entries, for 123456-1 and 123456-2.
So. Three sheets of data. They're pegged to the same number system, but I can't just copy-paste them side by side, because if Ruritanian has no word for butter, then the other languages' entries will be off by one going forwards, etc.
How can I get the data onto just one sheet, with the Filing Numbers as a truly central spine, so that there will be blank spaces as appropriate, so that I can just look at all the information side by side?
For each entry in each dictionary, each word is pegged to a unique number, so that theoretically you're matching the Ruritanian word for "butter" with the Freedonian word for "butter", except instead of synchronizing everything to the English word "butter", you've assigned "butter" to the Filing Number 123456. If a language has no word for butter, then that number might be skipped. If that language has a hard distinction between two different kinds of butter, then you might have two entries, for 123456-1 and 123456-2.
So. Three sheets of data. They're pegged to the same number system, but I can't just copy-paste them side by side, because if Ruritanian has no word for butter, then the other languages' entries will be off by one going forwards, etc.
How can I get the data onto just one sheet, with the Filing Numbers as a truly central spine, so that there will be blank spaces as appropriate, so that I can just look at all the information side by side?
Yeah, this is a classic use case for vlookup().
How do you want to handle it when there's a 123456-1 and 123456-2, though? Vlookup() is only going to return on an exact match. If it's simply sufficient to take whatever the first result you get, then you need to to just take the left six digits of the index number, using the formula left(A1,6) [or whatever the cell of your original index number is instead of A1] and vlookup against that.
If you need to show all of the possible 123456 results, first you'll have to figure out how you want to do that (concatenated together into one cell? in separate columns?) and then do some more manipulation to get there. It's not an ideal case for Excel and lookup formulas, but there are various kludges you can do depending on what you're looking for.
posted by strangely stunted trees at 11:25 AM on September 15, 2017 [1 favorite]
How do you want to handle it when there's a 123456-1 and 123456-2, though? Vlookup() is only going to return on an exact match. If it's simply sufficient to take whatever the first result you get, then you need to to just take the left six digits of the index number, using the formula left(A1,6) [or whatever the cell of your original index number is instead of A1] and vlookup against that.
If you need to show all of the possible 123456 results, first you'll have to figure out how you want to do that (concatenated together into one cell? in separate columns?) and then do some more manipulation to get there. It's not an ideal case for Excel and lookup formulas, but there are various kludges you can do depending on what you're looking for.
posted by strangely stunted trees at 11:25 AM on September 15, 2017 [1 favorite]
It sounds like Stitcherbeast wants 123456-1 and 123456-2 to live on separate rows, with a blank entry for language B if there's only one definition.
This means that in millipede's first step of including all of your Filing Numbers in column A means you'll have to take extra care to make sure you're capturing all the Filing Numbers used in all three data sheets.
That's not hard though--you can copy the Filing Number column from each of the three sheets into the new sheet, one below another (so you'll have 4500 rows at this point). Now just do a Remove Duplicates, then sort ascending, and you should have 1500 or so unique index entries to build your vlookups off of.
(Might need to be mindful of the cell formatting for that column though.)
Though depending on what the data looks like, you might have 123456, 123456-1, and 123456-2. And if language B and C both have a -1 and -2, do they map onto each other correctly?
posted by Pryde at 9:19 PM on September 19, 2017
This means that in millipede's first step of including all of your Filing Numbers in column A means you'll have to take extra care to make sure you're capturing all the Filing Numbers used in all three data sheets.
That's not hard though--you can copy the Filing Number column from each of the three sheets into the new sheet, one below another (so you'll have 4500 rows at this point). Now just do a Remove Duplicates, then sort ascending, and you should have 1500 or so unique index entries to build your vlookups off of.
(Might need to be mindful of the cell formatting for that column though.)
Though depending on what the data looks like, you might have 123456, 123456-1, and 123456-2. And if language B and C both have a -1 and -2, do they map onto each other correctly?
posted by Pryde at 9:19 PM on September 19, 2017
« Older helping a child be comfortable alone | What software will help accept and track donations... Newer »
This thread is closed to new comments.
You need a vlookup. Make a new sheet that includes all of your numbers in column A. In column b, do a vlookup to the Ruritan sheet (the Filing numbers have to be to the left of the words), and in column C, do a vlookup to the English sheet, and so on. You can just fill down the entire set of rows once you do this, and there you have it.
posted by millipede at 11:18 AM on September 15, 2017