ExcelFilter: Conditional copying between worksheets
October 19, 2011 4:58 PM Subscribe
ExcelFilter: I'd like to copy the contents of cells from one sheet to another, based on the value of a cell in the first sheet.
I'm struggling the proper vocabulary to Google this problem effectively.
I've got two worksheets: one has alphanumeric codes (like 4.B.1) sorted in columns, the second worksheet has the same codes in rows.
What I'd like is a formula that:
1. Takes a code in a cell on Sheet1,
2. Finds it in Sheet2,
3. Copies the content of the two cells to the right of the found code
4. Places that content in the cell below the cell in Sheet1.
I've got two worksheets: one has alphanumeric codes (like 4.B.1) sorted in columns, the second worksheet has the same codes in rows.
What I'd like is a formula that:
1. Takes a code in a cell on Sheet1,
2. Finds it in Sheet2,
3. Copies the content of the two cells to the right of the found code
4. Places that content in the cell below the cell in Sheet1.
I'd suggest a combination of offset and match, plus a little concatenation.
posted by rube goldberg at 5:28 PM on October 19, 2011
posted by rube goldberg at 5:28 PM on October 19, 2011
I'm not sure I fully understand what you're wanting to do. Could you give a small example?
One thing that might be helpful is to put the horizontal one into columns. You can do that by selecting it all, copying, and then pasting special with a "transpose".
posted by losvedir at 5:33 PM on October 19, 2011
One thing that might be helpful is to put the horizontal one into columns. You can do that by selecting it all, copying, and then pasting special with a "transpose".
posted by losvedir at 5:33 PM on October 19, 2011
Response by poster: vlookup is definitely the right technique, thanks empath.
But, I'm getting #N/A errors that I need to run down. I need to use FALSE for the range_lookup logic for an exact match, since I don't want to resort the data in the second sheet. I suspect they simply aren't exact matches.
posted by cramer at 5:59 PM on October 19, 2011
But, I'm getting #N/A errors that I need to run down. I need to use FALSE for the range_lookup logic for an exact match, since I don't want to resort the data in the second sheet. I suspect they simply aren't exact matches.
posted by cramer at 5:59 PM on October 19, 2011
One thing to watch out for if you're getting a lot of #N/A errors is defining the vlookup range either with a 1) named range or 2) absolute cell references.
So if your vlookup is something like =VLOOKUP(H2,A2:B100,...), that won't behave as expected because as you drag your formula to new cells, the corresponding range it's searching in will also slide around. So, using that same example, be sure to have dollar signs: =VLOOKUP(H2,$A$2:$B$100,...) in the range. (Or highlight the range ahead of time and give it a name, so it's something like =VLOOKUP(H2,my_range,...)).
posted by losvedir at 6:16 PM on October 19, 2011 [2 favorites]
So if your vlookup is something like =VLOOKUP(H2,A2:B100,...), that won't behave as expected because as you drag your formula to new cells, the corresponding range it's searching in will also slide around. So, using that same example, be sure to have dollar signs: =VLOOKUP(H2,$A$2:$B$100,...) in the range. (Or highlight the range ahead of time and give it a name, so it's something like =VLOOKUP(H2,my_range,...)).
posted by losvedir at 6:16 PM on October 19, 2011 [2 favorites]
VLOOKUP can also act quite funny if the data isn't as it expects. If you use the wrong TRUE/FALSE value for the final variable, or if even one lookup data value is out of order (when it's expecting data to be sorted), unpredictable results can occur.
TL;DR: Be careful with it. It can be both finicky, and fragile.
posted by IAmBroom at 7:03 PM on October 19, 2011
TL;DR: Be careful with it. It can be both finicky, and fragile.
posted by IAmBroom at 7:03 PM on October 19, 2011
Another VLOOKUP tidbit that can throw off the return of correct data is errant characters in either the looked up cells or the source cell.
A good starting point is to clean your data of obvious errors before doing the lookup at all. In doing so, you can rule out one small issue that can lead to really big errors in the return data. A simple example is extra spaces. Doing a lookup for "test123" is not the same as looking "test123 " (note the lack of a space after the 3). There are ways to look for wild cards and parts of a text string to get around this type of issue, but that is a whole different set of instructions. Other common errant characters are invisibles, soft returns and the mix up of variations on the simple dash. When you clean and make consistent the raw data, the rainy days go away, kids smile, food tastes better, your old clothes fit and you get fewer #N/A errors all at the same time. It is an amazing phenomenon.
I have posted this before, but it is worth another visit. I cannot endorse strongly enough ASAP Utilities for Excel. It is a collection of about 2.635 billion little utilities for doing all softs of things to data in Excel that you always wanted to do, but couldn't figure out or didn't want to scroll through endless board posts on the subject.
Plus, it is free for individual users, updated regularly and a very stable add-in. If you use it commercially, they ask that you pay for it. Totally worth the price.
Get it and clean your data first from ASAP's Text menu, write your formula and run the lookup. At least you will know your #N/A errors are not because of inconsistent data and your pants will fit.
posted by lampshade at 8:33 PM on October 19, 2011
A good starting point is to clean your data of obvious errors before doing the lookup at all. In doing so, you can rule out one small issue that can lead to really big errors in the return data. A simple example is extra spaces. Doing a lookup for "test123" is not the same as looking "test123 " (note the lack of a space after the 3). There are ways to look for wild cards and parts of a text string to get around this type of issue, but that is a whole different set of instructions. Other common errant characters are invisibles, soft returns and the mix up of variations on the simple dash. When you clean and make consistent the raw data, the rainy days go away, kids smile, food tastes better, your old clothes fit and you get fewer #N/A errors all at the same time. It is an amazing phenomenon.
I have posted this before, but it is worth another visit. I cannot endorse strongly enough ASAP Utilities for Excel. It is a collection of about 2.635 billion little utilities for doing all softs of things to data in Excel that you always wanted to do, but couldn't figure out or didn't want to scroll through endless board posts on the subject.
Plus, it is free for individual users, updated regularly and a very stable add-in. If you use it commercially, they ask that you pay for it. Totally worth the price.
Get it and clean your data first from ASAP's Text menu, write your formula and run the lookup. At least you will know your #N/A errors are not because of inconsistent data and your pants will fit.
posted by lampshade at 8:33 PM on October 19, 2011
Response by poster: losvedir, I quite quickly saw that I'd need to lock things down with $. Good tip.
lampshade, ASAP looks great, but I'm on a Mac. I think I'll give Google Refine a spin to clean up the data.
Thanks, everyone.
posted by cramer at 7:18 AM on October 20, 2011
lampshade, ASAP looks great, but I'm on a Mac. I think I'll give Google Refine a spin to clean up the data.
Thanks, everyone.
posted by cramer at 7:18 AM on October 20, 2011
« Older What is the volunteer-to-job path in politics? | LG Cosmos 2 VN 251: Exporting information Newer »
This thread is closed to new comments.
posted by empath at 5:05 PM on October 19, 2011 [1 favorite]