Excel Transposing the Vertical Data to Horizontal but following the filter rows

user_15941797 0
Viewed 60

Recently I have struggled to solve one problem regarding Transposing the data from vertical to horizontal, Following the filter rows pattern. So I have created A table with a few variables, I want to transfer the data so that it follows the next pattern: First name - Lucky Number - First name - Lucky Number, etc...

This is quite easy when the filters are off,

I would type some random letter (in this case h) following the Cell number, and then I would drag the data to the right: In the following example, I had to populate two cells ( first: hC1 second: hB1) I would select these two cells and would drag the cursor to the right, and Excel would recognize it and would follow the pattern, But With the filter on (Column Cool only YES answer displayed), it does not follow the pattern of filtered rows, so I was wondering is there a method to make excel follow the Filtered Rows?

enter image description here

transpose excel-formula excel
1 Answers

Please try using anyone of the either formulas depending on your Excel Version

FORMULA_SOLUTION

• Formula used in cell E13

=IFERROR(INDEX($A$2:$B$11,AGGREGATE(15,6,(ROW($D$2:$D$11)-ROW($D$2)+1)/
(($D$2:$D$11="YES")*(SUBTOTAL(3,OFFSET($D$2:$D$11,ROW($D$2:$D$11)-ROW($D$2),0,1))>0)),
INT((COLUMN(A1)-1)/2)+1),MOD(COLUMNS($A$1:A1)-2,2)+1),"")

The above formula, works for all versions of Excel, but needs to commit with CTRL+SHIFT+ENTER and fill right!

Perhaps, if you are using MS365/Excel 2021 then you may try the below formula as well,

=LET(Cool,$D$2:$D$11,
flist,SUBTOTAL(3,OFFSET(Cool,ROW(Cool)-ROW($D$2),0,1)),
array,FILTER($A$2:$B$11,((Cool="YES")*(flist>0)),""),
INDEX(array,INT((COLUMN(A1)-1)/2)+1,MOD(COLUMNS($A$1:A1)-2,2)+1))
user_81625200
Related