0

Hi I have an Excel Spreadsheet and I'm trying to convert a table with multiple rows and columns into just one column for sorting and filtering. But the issue is that it doesn't start at cell A2 where it's supposed to start, it starts at A2 of column C so it starts at the third letter, letter 'b' and skips the first two letters 'g' and 't'. Does anyone see an issue with the formula that's causing this?

Formula: =INDEX($A$2:$C$11, ROUNDUP(ROWS(G$2:G8)/3,0), MOD(ROWS(G$2:G8)-1,3)+1)

Screenshot:

excel spreadsheet screenshot

Improve this question
1
  • If you already have the row & column values you want in column H & I, why not reference those directly? =INDEX($A$2:$C$11,H2,I2) If that doesn't exist in your original data, it would make sense.
    – Engineer Toast
    Commented Feb 25, 2022 at 20:17

1 Answer 1

Reset to default
0

If you want to do this without the benefit of helper columns, then in cell G2 adjust your formula to:

=INDEX($A$2:$C$11,ROUNDUP(ROWS(G$2:G2)/3,0),1+MOD(ROWS(G$2:G2)-1,3))

and copy this down the remaining cells in column G.

The formula in your screenshot uses ROWS(G$2:G4) (not ROWS(G$2:G8) as specified in your question!!). You were almost there - just change the two instances of ROWS(G$2:G4) in your formula to ROWS(G$2:G2)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .