Compare two columns of identical names and pair them up

[domineaux]

Two columns of data - The A Column is the image name and the B Column is the product ID.
The column B is not a sorted list. The images are named identical to the product ID they are associated with.

I need to pair the image name with the Product Id, but I cannot sort the Column B, because it is further broken into categories that are a tier above the Product ID.

Please no VBA, because I don't know how to use it.

I have looked at match, index,vlookup, etc. I just cannot seem to get this working.

I am putting up data, not complete list...there should be plenty of pairings available.



Column A.........Column B
Image Name.......Product ID

REMO-00401.jpg
REMO-00402.jpg	REMO-12402
REMO-00501.jpg	REMO-12501
REMO-00601.jpg	REMO-12601
REMO-00602.jpg	REMO-12602
REMO-00701.jpg	REMO-12701
REMO-00702.jpg	REMO-12702
REMO-00801.jpg	REMO-12801
REMO-00901.jpg	REMO-12901
REMO-00902.jpg	REMO-12902
REMO-00903.jpg	REMO-12903
REMO-01001.jpg	REMO-13001
REMO-01101.jpg	REMO-13002
REMO-01102.jpg	REMO-13101
REMO-01103.jpg	REMO-13102
REMO-01201.jpg	REMO-10201
REMO-01202.jpg	REMO-10202
REMO-01301.jpg	REMO-10301
REMO-01401.jpg	REMO-10401
REMO-01501.jpg	REMO-10402
REMO-01502.jpg	REMO-10501
REMO-01503.jpg	REMO-10502
REMO-01601.jpg	REMO-10601
REMO-01602.jpg	REMO-10701
REMO-01701.jpg	REMO-10702
REMO-01801.jpg	REMO-10703
REMO-01802.jpg	REMO-10801
REMO-01901.jpg	REMO-10901
REMO-01902.jpg	REMO-10902
REMO-02001.jpg	REMO-08001
REMO-02101.jpg	REMO-08002
REMO-02102.jpg	REMO-08101
REMO-02103.jpg	REMO-08201
REMO-02201.jpg	REMO-08202
REMO-02202.jpg	REMO-08301
REMO-02301.jpg	REMO-08302
REMO-02401.jpg	REMO-08401
REMO-02501.jpg	REMO-08402
REMO-02502.jpg	REMO-08501
REMO-02503.jpg	REMO-00401
REMO-02601.jpg	REMO-00402
REMO-02701.jpg	REMO-00501
REMO-02702.jpg	REMO-00601
REMO-02703.jpg	REMO-00602
REMO-02801.jpg	REMO-00701
REMO-02802.jpg	REMO-00702
REMO-02901.jpg	REMO-00801
REMO-03001.jpg	REMO-00901
REMO-03101.jpg	REMO-00902
REMO-03102.jpg	REMO-00903
REMO-03103.jpg	REMO-01001
REMO-03201.jpg	REMO-01101
REMO-03202.jpg	REMO-01102
REMO-03301.jpg	REMO-01103
REMO-03401.jpg	REMO-01201
REMO-03501.jpg	REMO-01202
REMO-03502.jpg	REMO-01301
REMO-03503.jpg	REMO-01401
REMO-03601.jpg	REMO-01501
REMO-03602.jpg	REMO-01502
REMO-03701.jpg	REMO-01503
REMO-03702.jpg	REMO-01601
REMO-03801.jpg	REMO-01602
REMO-03802.jpg	REMO-01701
REMO-03901.jpg	REMO-01801
REMO-04001.jpg	REMO-01802
REMO-04101.jpg	REMO-01901
REMO-04102.jpg	REMO-01902
REMO-04103.jpg	REMO-02001
REMO-05401.jpg	REMO-02101
REMO-05402.jpg	REMO-02102
REMO-05501.jpg	REMO-02103
REMO-05601.jpg	REMO-02201
REMO-05602.jpg	REMO-02202
REMO-05701.jpg	REMO-02301
REMO-05702.jpg	REMO-02401
REMO-05801.jpg	REMO-02501
REMO-05901.jpg	REMO-02502
REMO-05902.jpg	REMO-02503
REMO-05903.jpg	REMO-02601
REMO-06001.jpg	REMO-02701
REMO-06002.jpg	REMO-02702
REMO-06101.jpg	REMO-02703
REMO-06102.jpg	REMO-02801
REMO-06201.jpg	REMO-02802
REMO-06202.jpg	REMO-02901
REMO-06301.jpg	REMO-03001
REMO-06302.jpg	REMO-03101
REMO-08001.jpg	REMO-03102
REMO-08002.jpg	REMO-03103
REMO-08101.jpg	REMO-03201
REMO-08201.jpg	REMO-03202
REMO-08202.jpg	REMO-03301
REMO-08301.jpg	REMO-03401
REMO-08302.jpg	REMO-03501
REMO-08401.jpg	REMO-03502
REMO-08402.jpg	REMO-03503
REMO-08501.jpg	REMO-03601
REMO-08801.jpg	REMO-03602

<tbody>
[TD="width: 98"] REMO-12401[/TD]

</tbody>

 

[Misca]

If the IDs and Image names are supposed to be identical why don't you just get the ID from the Image name (or vice versa)? Shouldn't make any difference but you'd get away with a lot easier formula.
But since you wanted to use the existing lists here's that formula as well:

Excel Workbook
	A	B	C
1	Image Name	Product ID	PRODUCT ID
2	REMO-00401.jpg	REMO-12401	REMO-00401
3	REMO-00402.jpg	REMO-12402	REMO-00402
4	REMO-00501.jpg	REMO-12501
Sheet

 

[domineaux]

The data in the spreadsheet will be converted to CSV after the sheet is done.

There is a huge data package to the right....columns (A1.AC1) with all the rows and colunns of information on the respective products. The products are first classified by category, which is the way they have to entered into the database. It will not be a one step upload to the database.

Each category of product will be uploaded separately.

This might be described as the first 40 product items are category A and the second 40 items are Cagegory B, etc.

Therefore it is best to pair the image column A with the Column B, keeping fixed postion of the column B.

It might help with verification of the dat, f I insert a column in front of column A, and change the image col to B, and product ID column to C.

Take a look at this see what you think. I appreciate your helps and prompt response on this. I've spent the past 4 hours trying to work through it, along with a few other issues.



Column A.......Column B...........Column C

-----------------Image------------Prod ID


Column B...............Column C

[TABLE="width: 168"]
<colgroup><col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4571;" width="125"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" width="98"> <tbody>[TR]
[TD="width: 125, bgcolor: transparent"]REMO-00401.jpg[/TD]
[TD="width: 98, bgcolor: transparent"] REMO-12401[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00402.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12402[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00501.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12501[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00601.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12601[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00602.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12602[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00701.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12701[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00702.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12702[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00801.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12801[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00901.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12901[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00902.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12902[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-00903.jpg[/TD]
[TD="bgcolor: transparent"] REMO-12903[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01001.jpg[/TD]
[TD="bgcolor: transparent"] REMO-13001[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01101.jpg[/TD]
[TD="bgcolor: transparent"] REMO-13002[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01102.jpg[/TD]
[TD="bgcolor: transparent"] REMO-13101[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01103.jpg[/TD]
[TD="bgcolor: transparent"] REMO-13102[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01201.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10201[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01202.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10202[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01301.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10301[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01401.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10401[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01501.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10402[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01502.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10501[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01503.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10502[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01601.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10601[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01602.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10701[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01701.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10702[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REMO-01801.jpg[/TD]
[TD="bgcolor: transparent"] REMO-10703
[/TD]
[/TR]
</tbody>[/TABLE]