Windows 7 PC running Excel 2010.
So I have a sheet with 20-30 thousand lines of info, most of which I do not need. I created a second sheet that consists of =if formulas to pull out only the info I needed, basically it transferred it so I have all of the info on one sheet, its just not sorted or in any kind of usable format. The info correlates to number 1-3000ish. The original sheet contains info for some of the lines, but not all of the 3000 will be filled in. I want to fill in what it has so i can go find the rest. I do not want to sort the sheet for a lookup formula due to other reasons.
so as an example
a b c d e
1 880 W 330 Hall
2
3
4 880 W 172 Hall
I am trying to pull out all the info for anything with an 880 in column A, but column C is not in order, so I can't use an =if(vlookup) configuration
So I have a sheet with numbers running down column C 1-3000 and would like to be able to pull in the info in the other columns from the unsorted sheet and plunk it down where it should be. Currently I am using:
=offset('sheet2'!c2,match(c2,'sheet2'!c4:c30000,0),2)
This works great...... but only for the very first line of information, after that it returns blanks. Not errors, but blank cells....
I am sure there is either an easy solution, or a cleaner way to do this..... but I really have run up against a wall with this one, even my trusty friend google couldn't show me the way, and he's hardly ever wrong.
TLDR: I am using an =offset with a match formula to pull in unsorted info onto a separate sheet, the first entry fills in fine, all other entries return blank cells instead of the actual info....
So I have a sheet with 20-30 thousand lines of info, most of which I do not need. I created a second sheet that consists of =if formulas to pull out only the info I needed, basically it transferred it so I have all of the info on one sheet, its just not sorted or in any kind of usable format. The info correlates to number 1-3000ish. The original sheet contains info for some of the lines, but not all of the 3000 will be filled in. I want to fill in what it has so i can go find the rest. I do not want to sort the sheet for a lookup formula due to other reasons.
so as an example
a b c d e
1 880 W 330 Hall
2
3
4 880 W 172 Hall
I am trying to pull out all the info for anything with an 880 in column A, but column C is not in order, so I can't use an =if(vlookup) configuration
So I have a sheet with numbers running down column C 1-3000 and would like to be able to pull in the info in the other columns from the unsorted sheet and plunk it down where it should be. Currently I am using:
=offset('sheet2'!c2,match(c2,'sheet2'!c4:c30000,0),2)
This works great...... but only for the very first line of information, after that it returns blanks. Not errors, but blank cells....
I am sure there is either an easy solution, or a cleaner way to do this..... but I really have run up against a wall with this one, even my trusty friend google couldn't show me the way, and he's hardly ever wrong.
TLDR: I am using an =offset with a match formula to pull in unsorted info onto a separate sheet, the first entry fills in fine, all other entries return blank cells instead of the actual info....
