software

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 advancejickes,

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 MVPquot;jickesquot; gt; wrote in message
...
gt; Here is my problem. I have lots of data in multiple columns that needs
gt; matched up. here is an example.
gt;
gt; col 1 col2 col3 col4 col5 col6 col7
gt; anderson 23 33 43 brook 44 55
gt; brook 34 55 4 zane 44 44
gt; kissell 55 44 4 kissell 55 65
gt;
gt; There are actually about 6 columns with names in them with with
gt; corresponding data in the next two columns. I need to search and match the
gt; names and put them in the same row. there will be some names in column 1
gt; that are not in column 5 and vice versa. Basically I want to sort the data
gt; so column 1 and 6 and all other columns with names and put them on the same
gt; row so I can compare the data in the columns that go with the names across
gt; the board. Hope I explained this correctly.
gt; Thanks in advance
gt;
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.

name1cnt1val1stk1name2cnt2val2name3val3stk3name4cnt4
wright 233343liengme 4455phillips 2333deitrick 82
lewis 34554zane 4444pearson 9281otten 59
kissell 55444kissell 5565sjoblom 5920peterson 68

namecntvalstkbins
1wright 233343
2lewis 34554
3kissell 55444
4liengme 4455
5zane 4444
6kissell 5565
7phillips .2333
8pearson .9281
9sjoblom .5920
10deitrick 82.
11otten 59.
12peterson 68.
set1

Assign names to all input columns with the format suggested above.
Select the input array (from name1 to 68) and
Insert gt; Name gt; Create gt; Top Row
Select the data array without the headers (from wright to 68) and
Insert gt; Name gt; Define gt; (name it array1)
Select the 5 cells in the lower array (name,cnt,val,stk,bins) and
Insert gt; Name gt; Create gt; 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(binsamp;(INT((set1-1)/rown) 1)))),quot;quot;,
INDEX(array1,MOD(set1-1,rown) 1,COLUMN(INDIRECT(binsamp;(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.