Transpose using formulas based on condition

[Sumit_123]

Hi, I have a situation where in I have data where the hierarchy goes as follows
Region>Country>Plant>crusher Name>Machine Score>Machine score check date.

is there a way to transpose the score 1 date below the most recent score column and the date next to it using formulas and likewise for other columns for score 3 date and etc..?
The catch is that the columns from A:E should be repeated for Score1 and date and likewise for score2 and date .

 

[Sumit_123]

Please see my edit to the post above. (Sorry for the confusion!) That "simplification" won't work. I spoke too soon! (The edits to post #8 DO apply. Just what I wrote (but now deleted) in post #9 was wrong.)

Thanks a lot for all your efforts. You are a life saver.?
Worked out for me. But I really want to nerd out and learn how you did it.
If you can point me as to where should I learn all this stuff, it would be awesome.

 

[cmowla]

Thanks a lot for all your efforts. You are a life saver.?
Worked out for me. But I really want to nerd out and learn how you did it.
If you can point me as to where should I learn all this stuff, it would be awesome.

Well a part of it is observing sequences associated with the INT() (which is actually FLOOR() = greatest integer function in math) and MOD() = modulus function in math.

For example, on a blank sheet in Excel, type in the following formula in any cell and press enter.

=SEQUENCE(1,20,1,1)

Next, in the cell beneath that one, this formula:

=MOD(SEQUENCE(1,20,1,1),5)

And in the cell beneath that one, this formula:

=INT(SEQUENCE(1,20,1,1)/5)

By observing those, and comparing that with what you wanted to do, you can see why I used them. I just have to do function transformations to match where your data started as well as how many columns needed to be cycled through, etc.

Regarding the rest, Google. I learned a lot of what I know from just "wrestling" with Excel.

If you have any specific question about any part of the formula that you would like to know where I learned it from, please ask.

 

[Sumit_123]

Thanks for pointing me in the right direction.
Just wanted to ask that if I add new rows or delete the rows, will everything in the formulas still work the same way?
Or If I want to add/delete rows what would be the best possible way to do this?

 

[cmowla]

Thanks for pointing me in the right direction.
Just wanted to ask that if I add new rows or delete the rows, will everything in the formulas still work the same way?
Or If I want to add/delete rows what would be the best possible way to do this?

Adding or deleting rows should not affect it. Just be sure to carry the formulas down to "display" the rows (if added).

 

[Sumit_123]

THANKS FLUFF FOR THE SOLUTION YOU PROVIDED, BUT I AM NOT THAT GREAT IN EXCEL YET. SO WASN'T REALLY SURE OF HOW TO USE AND INTERPRET YOUR SOLUTION. BUT REALLY THANKYOU FOR TAKING OUT TIME TO PROVIDE A SOLUTION.

 

[Sumit_123]

Adding or deleting rows should not affect it. Just be sure to carry the formulas down to "display" the rows (if added).

I tried deleting the rows but i believe its messing up the index formula.
Also when i tried adding rows in the middle of the data, the index is repeating.
Should I remove the formula in the Index column of original table?

 

[cmowla]

I tried deleting the rows but i believe its messing up the index formula.
Also when i tried adding rows in the middle of the data, the index is repeating.
Should I remove the formula in the Index column of original table?

Feel free to remove the formula if you wish, but the numbers (starting with 2) need to be in the index column for this to work. So if you delete all of the formulas under the index column heading, an easy way to get the numbers back is to type 2 in the first cell under the heading and 3 in the cell under that. Then select/highlight both the two and three, and then click "fill down" down to the last row in the table.

 

[cmowla]

@Sumit_123,

Feel free to remove the formula if you wish, but the numbers (starting with 2) need to be in the index column for this to work. So if you delete all of the formulas under the index column heading, an easy way to get the numbers back is to type 2 in the first cell under the heading and 3 in the cell under that. Then select/highlight both the two and three, and then click "fill down" down to the last row in the table.

On second thought, a way that you can have the rows autoupdate (even if you insert new rows or delete new rows) is to change the formula in (ALL) cells in the index column (2 to n) to the following.

=ROW()

The only "restriction" is that the table headings must be in row #1. Otherwise, if your table is a little down your sheet (and the cell which should be 2 is in row 8 instead (whereby the above formula will put an 8 there), you need to have

=ROW()-6

instead. But once you establish the vertical "altitude" of where your table starts, you won't have to ever change this formula again. (Let the Excel table autofill as mentioned in the video . . . or select all (current) cells in the Index column and press Ctrl D.)

 

[Sumit_123]

@Sumit_123,



On second thought, a way that you can have the rows autoupdate (even if you insert new rows or delete new rows) is to change the formula in (ALL) cells in the index column (2 to n) to the following.

=ROW()

The only "restriction" is that the table headings must be in row #1. Otherwise, if your table is a little down your sheet (and the cell which should be 2 is in row 8 instead (whereby the above formula will put an 8 there), you need to have

=ROW()-6

instead. But once you establish the vertical "altitude" of where your table starts, you won't have to ever change this formula again. (Let the Excel table autofill as mentioned in the video . . . or select all (current) cells in the Index column and press Ctrl D.)

This Row function works beautifully. Thankyou so much again