Match Data in multiple rows&columns

  • Thread starter Thread starter powderskier9
  • Start date Start date
P

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
 
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
 
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
 
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!
 
_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
 
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)
 
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
 
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
 
Back
Top