MATCH UP DATA IN COLUMNS

[jickes]

Here is my problem. I have lots of data in multiple columns that needs
matched up. here is an example.

col 1 col2 col3 col4 col5 col6 col7
anderson 23 33 43 brook 44 55
brook 34 55 4 zane 44 44
kissell 55 44 4 kissell 55 65

There are actually about 6 columns with names in them with with
corresponding data in the next two columns. I need to search and match the
names and put them in the same row. there will be some names in column 1
that are not in column 5 and vice versa. Basically I want to sort the data
so column 1 and 6 and all other columns with names and put them on the same
row so I can compare the data in the columns that go with the names across
the board. Hope I explained this correctly.
Thanks in advance

 

[Bernie Deitrick]

jickes,

Here's a way to do it using formulas and sorting.

You would need to set up a helper column: if you wanted to match names in Columns E to names in
Column A, then in cell H2 (let's say that your data starts on row 2, and you have two columns of
data that needs to stay with values in column E, and your data extends for 100 rows in column A),
use the formula

=IF(ISERROR(MATCH(E2,$A$2:$A$100,FALSE)),COUNTA($A$2:$A$100)+1,MATCH(E2,$A$2:$A$100,FALSE))

and copy down to match your data in column E.

Then, in the cell below the last formula in column H - let's say your last cell was H124, so in
H125, enter the formula

=IF(ISERROR(MATCH(ROW(A1),$F$2:$F$124,FALSE)),ROW(A1),1000)

and copy down for at least 100 rows.

Then copy column H, pastespecial values, and then sort columns E, F, G, and H based on column H,
and, finally, delete column H.

Of course, a macro could be used as well, but since you posted in worksheetfunctions, we won't use
macros.

HTH,
Bernie
MS Excel MVP

 

[Herbert Seidenberg]

Here is a variation, with these criteria:
Formulas do not refer to rows/columns directly and
do not change in appearance when moved.
The number of name columns in this example is 4 and is expandable.
The number of associated value columns can be variable and is
expandable.
The output can be sorted without losing the formula and dependents.

name1 cnt1 val1 stk1 name2 cnt2 val2 name3 val3 stk3 name4 cnt4
wright 23 33 43 liengme 44 55 phillips 23 33 deitrick 82
lewis 34 55 4 zane 44 44 pearson 92 81 otten 59
kissell 55 44 4 kissell 55 65 sjoblom 59 20 peterson 68

name cnt val stk bins
1 wright 23 33 43
2 lewis 34 55 4
3 kissell 55 44 4
4 liengme 44 55
5 zane 44 44
6 kissell 55 65
7 phillips . 23 33
8 pearson . 92 81
9 sjoblom . 59 20
10 deitrick 82 .
11 otten 59 .
12 peterson 68 .
set1

Assign names to all input columns with the format suggested above.
Select the input array (from name1 to 68) and
Insert > Name > Create > Top Row
Select the data array without the headers (from wright to 68) and
Insert > Name > Define > (name it array1)
Select the 5 cells in the lower array (name,cnt,val,stk,bins) and
Insert > Name > Create > Right Column
Create and name the set1 (from 1 to rows(array1)*(number of name
columns))
Add the name
rown Refers to =ROWS(array1)
Fill the lower array with this formula:
=IF(ISERROR(COLUMN(INDIRECT(bins&(INT((set1-1)/rown)+1)))),"",
INDEX(array1,MOD(set1-1,rown)+1,COLUMN(INDIRECT(bins&(INT((set1-1)/rown)+1)))))
You can expand the associated values by inserting columns in the arrays
and
naming them consistent with the present format.
When sorting, be sure to include set1.