Match Data in multiple rows&columns

[powderskier9]

COLMN A COLMN B COLMN C COLMN D

Number Descrp. Number Descrp.

A-1000 Item 0 A-1010 Item 1
A-1010 Item 1 A-1030 Item 3
A-1020 Item 2 A-1040 Item 4
A-1030 Item 3 A-1050 Item 5
A-1040 Item 4
A-1050 Item 5

OK, there has to be a way to do this. I cannot figure it out. I need t
match the location of the Data in Column C & D to the location of dat
in Column A & B. For example, A-1010 in Column C and Item 1 in Column
need to be on the same row as A010 and Item 1 in Columns A & B(row 2 o
data). If someone knows how to do this I will be very grateful. I hae
included a sample excel file as well to illustrate what I need done.

Thanks,

Mar

Attachment filename: excelproblem.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47679

 

[Frank Kabel]

Hi Mark
and what do you want to return. If you only want to check, if there's a
match use the following array formula (entered with CTRL+SHIFT+ENTER)
in E1

=IF(ISNA(MATCH(C1&D1,$A$1:$A$100&$B$1:$B$100,0)),"no match","match")
and copy down

 

[powderskier9]

Hi Frank,

Thanks for your prompt reply. Basically I want to align the data i
Columns C and D with their counterparts in Columns A and B. So, as i
my example, A-1010 in Column C, would move down a row to match th
position of A-1010 in Column A. I need to move the description colum
as well. I was thinking this might be accomplished by copying the dat
to another sheet by this didn't work for me.

Thanks,

Mar

 

[Frank Kabel]

Hi Mark

Why do you want to move cells (quite difficult with formulas). If you
only want to combine data this can easily be done. Best way:
- post a complete example row / data set (as plain text) as you have it
currently in your sheet and post your expected results for some rows!

 

[powderskier9]

_Example_of_Live_Layout:_

A-1000 AHM Briefcase/carry bag natural A-1020 AHM Hemp Twine-20l
black
A-1010 AHM Hemp Twine-20lb natural A-1040 AHM Hemp Twine-20lb red
A-1020 AHM Hemp Twine-20lb black A-1050 AHM Hemp Twine 48lb natural
A-1030 AHM Hemp Twine-20lb green A-1070 AHM Yarn-200g (medium) natural
A-1040 AHM Hemp Twine-20lb red A-1040 AHM Hemp Yarn - 500
(large)natural

_Example_of_Desired_Layout:_

A-1000 AHM Briefcase/carry bag natural
A-1010 AHM Hemp Twine-20lb natural A-1010 AHM Hemp Twine-20lb natural
A-1020 AHM Hemp Twine-20lb black A-1020 AHM Hemp Twine-20lb black
A-1030 AHM Hemp Twine-20lb green
A-1040 AHM Hemp Twine-20lb red A-1040 AHM Hemp Twine-20lb re

 

[Frank Kabel]

Hi
using a little workaround and some helper columns
Try:
- Insert new columns C + D as helper columns. Your second list becomes
now column E + F
- In C1 enter the following formula:
=IF(ISNA(MATCH(A1&B1,$E$1:$E$100&$F$1:$F$100,0)),"",A1)
copy this formula down and one column to the right

- After this you may want to replace the formulas with their value:
-> select column C+D
-> copy them (CTRL+C)
-> goto 'Edit - Paste Special' and choose 'Values')

- You also an delete the columns E+F (the former columns C+D)

 

[powderskier9]

Hey Frank,

Thanks for the prompt reply. The solution that you provided worked fo
the current problem that I stated. I have another issue to addres
within that spreadsheet. I have another column as shown below wit
unique values in it(Quantity). These values need to be retained whe
the data in the columns is matched. I have attached another xls fil
that contains the sample and desired layouts. Thanks again for all o
your expertise.

Sample Layout:
Number Description Quantity Number Description Quantity
A-1000 AHM Briefcase/carry bag natural (0.00) A-1020 AHM Hem
Twine-20lb black 1.00
A-1010 AHM Hemp Twine-20lb natural 13.00 A-1050 AHM Hemp Twine 48l
natural 2.00
A-1020 AHM Hemp Twine-20lb black 9.00
A-1030 AHM Hemp Twine-20lb green (0.00)
A-1040 AHM Hemp Twine-20lb red 3.00
A-1045 AHM Twine 1kg Spool 1.00
A-1050 AHM Hemp Twine 48lb natural 11.00

Desired Layout:
Number Description Quantity Number Description Quantity
A-1000 AHM Briefcase/carry bag natural (0.00)
A-1010 AHM Hemp Twine-20lb natural 13.00
A-1020 AHM Hemp Twine-20lb black 9.00 A-1020 AHM Hemp Twine-20l
black 1.00
A-1030 AHM Hemp Twine-20lb green (0.00)
A-1040 AHM Hemp Twine-20lb red 3.00
A-1045 AHM Twine 1kg Spool 1.00
A-1050 AHM Hemp Twine 48lb natural 11.00 A-1050 AHM Hemp Twine 48l
natural 2.0

 

[powderskier9]

Seems that if the information in the columns is transposed into a row
then sorted based on rows vs column, left to right, the values are the
sorted as required. Codes/Descriptions/Quantities line up next to on
another. Is there a way to then convert the information in the row
back into column format maintaining there position relative to on
another.

Cheers