Formula to Align Identical Rows, and Rows which match in Part

Goldenboy23

New Member
Joined
Oct 17, 2017
Messages
11
Dear all,

Fairly new to excel, and have been trying for several days to solve this one with no success. I have a scenario where i have several different columns (which are in reality account strings) and i am required to match them across each row. My issue is that not all rows will have information in them, and there is no one single column which will necessarily be complete. Therefore a simple V lookup will not assist. However, there are two columns (D&E) which when combined do contain all the information and i think can be used a driver for all the other columns.

Please see a simpler illustration (below) of my problem and the result i wish to achieve;

Problem;

[TABLE="width: 320"]
<colgroup><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"></colgroup><tbody>[TR]
[TD="class: et2, width: 64"]A[/TD]
[TD="class: et2, width: 64"]B[/TD]
[TD="class: et2, width: 64"]C[/TD]
[TD="class: et3, width: 64"]D[/TD]
[TD="class: et3, width: 64"]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et4, width: 64"]1[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]2[/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD="class: et4, width: 64"]3[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et1, width: 64"]3[/TD]
[TD][/TD]
[TD="class: et4, width: 64"]2[/TD]
[TD="class: et4, width: 64"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD][/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et4, width: 64"]6[/TD]
[TD="class: et4, width: 64"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]6[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]7[/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD][/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]7[/TD]
[/TR]
</tbody>[/TABLE]


The end result i wish to see;

[TABLE="width: 320"]
<colgroup><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"></colgroup><tbody>[TR]
[TD="class: et2, width: 64"]A[/TD]
[TD="class: et2, width: 64"]B[/TD]
[TD="class: et2, width: 64"]C[/TD]
[TD="class: et3, width: 64"]D[/TD]
[TD="class: et3, width: 64"]E[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et1, width: 64"]1[/TD]
[TD="class: et4, width: 64"]1[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]2[/TD]
[TD="class: et1, width: 64"][/TD]
[TD="class: et4, width: 64"]2[/TD]
[TD="class: et4, width: 64"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]3[/TD]
[TD][/TD]
[TD="class: et4, width: 64"]3[/TD]
[TD="class: et4, width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD="class: et1, width: 64"]4[/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]4[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et1, width: 64"][/TD]
[TD="class: et1, width: 64"]5[/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD="class: et1, width: 64"]6[/TD]
[TD="class: et4, width: 64"]6[/TD]
[TD="class: et4, width: 64"]6[/TD]
[/TR]
[TR]
[TD="class: et1, width: 64"]7[/TD]
[TD="class: et1, width: 64"][/TD]
[TD][/TD]
[TD="class: et4, width: 64"][/TD]
[TD="class: et4, width: 64"]7[/TD]
[/TR]
</tbody>[/TABLE]

I think an index and match may the solution, but i am just not quite able to fully solve it. P.S (the above is just an illustration for me to apply)

Any help will be greatly appreciated.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Assuming sample data in range A1:E7, try this:

[TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]=IFERROR(INDEX($A$1:$E$7,MATCH(ROW($A1),A$1:A$7,0),COLUMN(A$1)),"")[/TD]
[/TR]
</tbody>[/TABLE]

Change range accordingly and copy across and down.

Godspeed!
 
Upvote 0
Hello friend, is there a away of adapting the formula to return a value rather than a relative position, e.g. if all the 1 were 100 and all the 2 were 250 etc ... many thanks?
 
Upvote 0
Hi, Goldenboy23!
There may be easier ways to do that, but this is what I have come up with.

Assuming sample data in range A2:E8, I use two help columns, say F and G. These columns can be hidden.

In F2, use formula below to rank all values in one column. Copy it down until it results blank cells.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]
Code:
=IFERROR(SMALL($A$2:$E$12,ROW(A1)),"")
[/TD]
[/TR]
</tbody>[/TABLE]

In G2, array enter formula below (paste and press ctrl+shift+enter keys simultaneously). Copy it down until it results blank cells.

Code:
[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IFERROR(INDEX($F$2:$F$100,MATCH(1,(COUNTIF(G$1:G1,$F$2:$F$100)=0)*($F$2:$F$100<>""),0)),"")[/TD]
[/TR]
</tbody>[/TABLE]

In H2, use formula below to list your values in desired order. Copy it across and down.

Code:
[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IFERROR(INDEX($A$2:$E$8,MATCH($G2,A$2:A$8,0),COLUMN(A$1)),"")[/TD]
[/TR]
</tbody>[/TABLE]

I hope this will work!
Godspeed!
 
Upvote 0
Hello, Goldenboy23!

Check this out and see if it will shed some light on your doubts.

Columns P thru T are your 5 sheets ranked values.
Column V are your ranked values and
Column U are unique ranked values.

Feel free to adjust ranges length.
I hope it will work, my friend!

https://we.tl/PI99jNt2CY

Godspeed!
 
Upvote 0
One thing I forgot to mention: watch for columns headings match sheets titles, so the INDIRECT function will work.
 
Upvote 0
Thank you for this, it will be of great help. I am attempting to use the front sheet as a template, where the codes/strings on each of the other sheets change every week/month and i insert the new raw information on the other sheets and this updates the front sheet . . Is this something that is possible?
 
Upvote 0
You're welcome!
I guess it is, as long as you change ranges in all sheets and columns (including help columns) accordingly.
Feel free to ask for help if something goes wrong, and I'll see what can be done.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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