Consolidate Rows and Columns with Data

Would this be possible.
In attached, I want to consolidate data from columns only with data in.
I have 2 options for this in attached : Results 1 and Results 2, any of them will work
Thanks

Using a couple of helpers, put this formula in C2:

=IF(COUNTIF(C4:C51,"?*")=0,"-",MAX($B2:B2)+1)

and copy across to N2, then use this formula in A4:

=IF(COUNTIF(C4:N4,"?*")=0,"-",MAX(A$3:A3)+1)

and copy down to A51.

Then you could have this formula in AH3:

=IFERROR(INDEX($C$3:$N$3,MATCH(COLUMNS($AH:AH),$C$2:$N$2,0)),"")

and copy this across until you start to get blanks. Then use this formula in AG4:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),A:A,0)),"")

Copy this down until you get blanks.

Finally, put this formula in AH4:

=IFERROR(INDEX($C$4:$N$51,MATCH($AG4,$B$4:$B$51,0),MATCH(AH$3,$C$3:$N$3,0)),"")

Copy into AI4 (no need to copy further across, as row 2 will show that there are only 2 columns used), then copy AH4:AI4 down until both start to show blanks.

You can delete columns P to AF if you no longer need them.

Hope this helps.

Pete

Both works !
Thank you

Glad to help, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete