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

FrostOnRoads

New Member
Joined
Nov 2, 2012
Messages
2
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
Code:
=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.
Code:
=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).
 
Upvote 0
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 :laugh:
 
Upvote 0
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
ABCDEFGHIJ
1035
212aaaa1311aaaa
3212bbbb2422bbbb
4104cccc2434eeee
5711dddd25412bbbb
641eeee35511dddd
7356
8357
Sheet7
 
Upvote 0
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 :-)
 
Upvote 0

Forum statistics

Threads
1,223,061
Messages
6,169,873
Members
452,287
Latest member
winnievmex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top