Sort Columns B-D based on matching A to B, with blanks

[FrostOnRoads]

Hey guys I've got an issue with data sorting. My data looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]aa
[/TD]
[TD]aa
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12
[/TD]
[TD]bb
[/TD]
[TD]bb
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]4
[/TD]
[TD]cc
[/TD]
[TD]cc
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]11
[/TD]
[TD]dd
[/TD]
[TD]dd
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]ee
[/TD]
[TD]ee
[/TD]
[/TR]
</tbody>[/TABLE]

I need it to look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]ee
[/TD]
[TD]ee
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]aa
[/TD]
[TD]aa
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]cc
[/TD]
[TD]cc
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12
[/TD]
[TD]bb
[/TD]
[TD]bb
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11
[/TD]
[TD]dd
[/TD]
[TD]dd
[/TD]
[/TR]
</tbody>[/TABLE]

Basically I need to resort B based on matches in A, leaving blanks where there are no matches. C and D are tied to B, and cells that exist in B but not in A should go to the bottom. I feel like there should be a way to do this without writing a macro. Columns A and B will be different lengths. I don't mind a two step process, though it's not preferable.

Any help is appreciated!

 

[Gerald Higgins]

Hi, welcome to the board.

Are the values in col A unique ?
Are the values in col B unique ?
Do the values in col A exactly match those in col B (no decimal place issues) ?

If the answers are all YES, then this might be a solution.

I would suggest leaving your original data unchanged to begin with, and putting the formulas below in a new range off to the right.
Once you're done, if you're happy with the results, use Copy, Paste Special, Values, to convert the results of these formulas to values, and delete or hide your original columns B,C and D.

This formula will repeat the value in col A, IF that value is found anywhere in col B
Let's say you put this formula in col E.

=if(iserror(vlookup(a1,b$1:b$100,1,false)),"",vlookup(a1,b$1:b$100,1,false))

Adjust the range if the last row is not 100.

Then this formula will return the "ee" value where appropriate.

=if(iserror(vlookup(e1,b$1:d$100,2,false)),"",vlookup(e1,b$1:d$100,2,false))

Adjust the range if the last row is not 100.

Then use a version of the above for the second "ee" value.

Copy all formulas down as far as required.

I haven't tested this, but this general approach should work (although there may be better options).

 

[FrostOnRoads]

Wow that works like a charm! Now if you could give the formula that will return the several hours of my life I spent on this back, that would be wonderful

 

[Peter_SSs]

Welcome to the MrExcel board!

I can't see how the suggestion produces the 12 and 11 at the bottom of your data per your sample. Have I missed something?

This also doesn't exactly match your sample layout but see if it is any use.

E1 houses a 0.
Formula in F1 is copied across to G1.
Formulas in E2 and F2 are copied down.
G2 and G3 are entered manually then selected and dragged down to produce the sequence shown in column G.
Formula in H2 is copied down.
Formula in I2 is copied across to J2 and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1					0	3	5
2	1	2	aa	aa	1	3	1	1	aa	aa
3	2	12	bb	bb	2	4	2	2	bb	bb
4	10	4	cc	cc	2	4	3	4	ee	ee
5	7	11	dd	dd	2	5	4	12	bb	bb
6	4	1	ee	ee	3	5	5	11	dd	dd
7					3	5	6
8					3	5	7
Sheet7

 

[Gerald Higgins]

Peter, you're right, my suggestion would not have delivered the 12 and 11 at the bottom of the OP. I missed that requirement.

On the other hand, see post #3