Formula to reverse column orders and align

[joshaer]

Hi,

I have data that looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]g[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and I need a formula to convert it to look like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]d[/TD]
[TD]c[/TD]
[TD]b[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]d[/TD]
[TD]c[/TD]
[TD]g[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]d[/TD]
[TD]i[/TD]
[TD]h[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

(i.e. the order reversed, and the last values (as per first table) all in the same column).

If it helps, the letters represent individuals, and the chain is the reporting chain (i.e. "d" is CEO of the organisation, so eventually every employee ends up reporting to "d". The challenge is that it's through different length strings.

For each individual, I have a unique ID and a unique ID for their manager. I assemble the top table by looking up the manager ID in the list of employee IDs and returning the associated manager ID (i.e. manager's manager) and so on until I get to the end ("d"). I then need to work out how to turn this around into the opposite structure, where all the "d"s are in the same column.

As I will have to hand this over to multiple people, there is a very strong preference not to use any VBA.

Thanks!

 

[steve the fish]

This works for your example data:

=IFERROR(INDEX($A1:$D1,1+COUNTA($A1:$D1)-COLUMNS($A1:A1)),"")

 

[joshaer]

Thank you!