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

[Goldenboy23]

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.

 

[estevaoba]

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!

 

[Goldenboy23]

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?

 

[estevaoba]

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"]

=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.

[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.

[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!

 

[Goldenboy23]

Hi thank you for this... i have sent you a private message☺

 

[estevaoba]

OK. I’ll take a look.

 

[estevaoba]

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!

 

[estevaoba]

One thing I forgot to mention: watch for columns headings match sheets titles, so the INDIRECT function will work.

 

[Goldenboy23]

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?

 

[estevaoba]

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.