Cross Reference item from one colum to items in a new column

**mbrackey** · 02-07-2013, 01:42 PM

I am trying to figure out a process in which I can filter a large excel document that has one column with current asset numbers & serial numbers. What I need to do in this process is line up the first four digits of a caolumn, with the four digit entries on another column. But, the spreadsheet is a fluid document, in that the information changes when an asset changes locations, which happens rather frequently. Basically what I need to know is a way to filter this document so the the information in the primary row, (Column A) will adjust to line up with the information in another column, without throwing the whole system out of whack.

**ChemistB** · 02-07-2013, 02:23 PM

So, in col A you have a series of numbers like this
12345343
12562345
25313456
and in some other column (lets say D) you have 4 digit codes like this
2531
1234
1342
1256
And you want the values in Col A to be in the same rows as the codes in col D. Do I have that right?

Try something like this (if you have trouble, supply more exact info on data locations)

=IF(SUMPRODUCT((LEFT($A$1:$A$100,4)+0=D1)*ROW($A$1:$A$100))>0, INDEX($A$1:$A$100, SUMPRODUCT((LEFT($A$1:$A$100,4)+0=D1)*ROW($A$1:$A$100)),"")

**mbrackey** · 02-07-2013, 03:08 PM

I am just looking for a filter to rearrange the table, to align the data in column D (for example) with the source data in the first four digits of column A (for example).

**ChemistB** · 02-07-2013, 03:36 PM

That would require VBA. Are you okay with that? There's no inherient filter that would do that. Here is an example of how my formula would work. You could then Copy > Paste Special Values in Col B to remove the formulas and then delete Col A.