Excel 2007 Compare Two Col, Keep Sort Order

March 3, 2011 at 08:03:16
Specs: Windows XP

I could really use some expert assistance on a problem I'm having trying to find a macro to do what I need to do and if anyone could assist, it would be greatly appreciated.

I'm posting this particular problem on this forum, because I've seen a lot of similar solutions to the problem I have posted here already, but not close enough where I can copy the code and make it work for myself.

I have two columns of Part Numbers from two similar indented Bills of Material. These list can be as long as 18K rows. From the top down, I need to go down each of the list and put the matching entries from column A on the same row as the matching entry in column B or vise-versa, (leaving spaces adjacent to non-matches). The kicker here is that the sort order of either column can not change, because that would interfere with the identity of the product structure. Therefore, there will be blank cells next to those Part Numbers that appear only on one Bill of Material. So the only thing that will happen is that things will shift down in each column until there are matching aligning cells in a row. Seems odd, but the whole idea here is that someone can take two BOM’s for similar products and have a side-by-side comparison. The matches will be easy to spot because they will be directly beside each other.

In a nut shell if you were to go back and remove the blank cells from both columns, you would be right back where you started, because the order of each list will still be the same. Again, if someone can help me with this I would certainly appreciate it.

To try and post an example of the data would be too difficult. However, I could provide an example in Excel if someone would like mt to forward it. I don't see how to attach a file here on the forum or I just don't understand how it is done.



See More: Excel 2007 Compare Two Col, Keep Sort Order

March 3, 2011 at 12:22:37
Sorry, having problems understanding what you need.

Post a small example of your data ( 10 or 15 lines, with headings and row numbers) if you can give a before and after, but first read this on how to do it:




Report •

March 3, 2011 at 13:04:05
Hi Mike,

Thanks for looking at my post. I hope this is enough of an example to convey what I'm looking for though...

I've put a comma ( , ) after each column A entry for seperation so that this doesn't get all jumbled up when I post. I don't know of any other way to get clear spacing in a post.

</ColumnA			ColumnB
ComponentPart ComponentPart

CBN5305-103 CBN5307-101
1K17-3-3-3 1K17-3-3-3
500-5-531-39 500-5-531-109
500-5-531-3 500-5-531-53
311-3DD M5593-3333
M5593-33 311-3DWW
311-3DW MS57707-35DD
MS57707-35D 500-5-531-39
5513B/AGRAY 500-5-531-3
755-3D1-5 311-3DD
501-5-005-1 M5593-33
7075-T3511QQ-A-500/1130-7577 311-3DW
501-5-131-1 MS57707-35D
501-5-153-59 755-3D1-5
311-3D33 501-5-005-1
501-5-153-5 7075-T3511QQ-A-500/1130-7577
M5593-355 501-5-131-1
MS57707-3DHH 501-5-153-59
501-5-737-19 311-3D33
5311-3D 501-5-153-5
501-5-737-3 M5593-355
M5593-399 MS57707-3DHH
MS57707-3DDDD 501-5-737-9
501-5-737-9 311-3D
311-3D 501-5-737-1
501-5-737-1 M5593-3
M5593-3 MS57707-3D
MS57707-3D 501-5-735-9


ColumnA ColumnB
ComponentPart ComponentPart

CBN5305-103 BlankCell
BlankCell CBN5307-101
BlankCell 500-5-531-109
BlankCell 500-5-531-53
BlankCell M5593-3333
BlankCell 311-3DWW
BlankCell MS57707-35DD
500-5-531-39 500-5-531-39
500-5-531-3 500-5-531-3
311-3DD 311-3DD
M5593-33 M5593-33
311-3DW 311-3DW
MS57707-35D MS57707-35D
5513B/AGRAY BlankCell
755-3D1-5 755-3D1-5
501-5-005-1 501-5-005-1
7075-T3511QQ-A-500/1130-7577 7075-T3511QQ-A-500/1130-7577
501-5-131-1 501-5-131-1
501-5-153-59 501-5-153-59
311-3D33 311-3D33
501-5-153-5 501-5-153-5
M5593-355 M5593-355
MS57707-3DHH MS57707-3DHH
501-5-737-19 BlankCell
5311-3D BlankCell
501-5-737-3 BlankCell
M5593-399 BlankCell
MS57707-3DDDD BlankCell
501-5-737-9 501-5-737-9
311-3D 311-3D
501-5-737-1 501-5-737-1
M5593-3 M5593-3
MS57707-3D MS57707-3D

Report •

March 3, 2011 at 13:39:43
Did you read the HOW-TO link I posted?
It explains how to post Excel Tables and VBA codes in these forums.

I'll look at what you posted and see what I can do.



Report •

Related Solutions

March 3, 2011 at 13:46:37
From a quick look, your going to need VBA code to accomplish what you need.

I can get matching lines, IE; Row 15 Column A matches Row 19 Column B,
but cannot move the data with just formulas.

Unfortunately my VBA code skill are about nill.

Hopefully someone else will offer some assistance.



Report •

March 3, 2011 at 16:29:29

I've gone back and used the instruction on how to post the sample. I think it is a little better and can be read now. Anyone that can help me with code to get this to work please do.

Thank you,


Report •

Ask Question