• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

transpose values based on select criteria

M

mlttkw

New Member
Hi...i'm trying to create a repeatable transpose from an array in which the order of the data could change. without writing a vb script (which would cause my simple mind headaches) i thought i could use a combination of the Transpose and IF function. however, i'm having issues..

the data to transpose would be determined by the text value in column A in the Array.

in the example below i would like to only transpose the data on another worksheet where column A equals "SPSBASE" and transpose values d3:j9

1 a b c d e f g h i j
2 Scenario mrg_qtr bqr_seg_l2 EXP_1 EXP_2 EXP_3 EXP_4 EXP_5 EXP_6 EXP_7
3 SPSADV 2016Q2 CORP 18 19 20 21 22 23 24
4 SPSADV 2016Q3 CORP 19 20 21 22 23 24 25
5 SPSADV 2016Q4 CORP 20 21 22 23 24 25 26
6 SPSBASE 2013Q4 CORP 1 2 3 4 5 6 7
7 SPSBASE 2014Q1 CORP 9 10 11 12 13 14 15
8 SPSBASE 2014Q2 CORP 10 11 12 13 14 15 16
9 SPSBASE 2014Q3 CORP 11 12 13 14 15 16 17
 

Attachments

  • Capture.PNG
    Capture.PNG
    62.4 KB · Views: 5
SirJB7 said:
Hi, mlttkw!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!
Click to expand...
Thank you very much for the insight and advice...i did take a screenshot but as you suggested the before and after is more desirable i'm attaching that now
thanks again
 

Attachments

  • example.xlsx
    11 KB · Views: 6
Hi, mlttkw!

Give a look at the uploaded file.

I removed the descriptive header and shifted right the output example so as to keep the data array starting at cell A1 and nothing else in column A, and added a last helper column at the end of the data.

It uses 5 dynamic named ranges for easier referencing at formulas:

DataTable: =DESREF(Sheet1!$A$2;;;CONTARA(Sheet1!$A:$A)-1;CONTARA(Sheet1!$1:$1)) ------> in english: =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))

HelperCountList:=DESREF(DataTable;;COLUMNAS(DataTable)-1;;1) -----> in english: =OFFSET(DataTable,,COLUMNS(DataTable)-1,,1)

TransposeTable: =DESREF(DataTable;;3;;7) -----> in english: =OFFSET(DataTable,,3,,7)

TransposeTitleList: =DESREF(TransposeTable;-1;;1;) -----> in english: =OFFESET(TransposeTable,-1,,1,)

FilterCell: =Sheet1!$B$19

Alternatively and considering that 1st header of EXP columns is fixed and known and that 1st header of BOOK columns (range following EXP) is fixed and known too, you can make TransposeTable definition even more dynamic, as stated in sample range Transpose2Table:

Transpose2Table: =DESREF(DataTable;;COINCIDIR("EXP_1";DESREF(DataTable;-1;;1;);0)-1;;COINCIDIR("BOOK_1";DESREF(DataTable;-1;;1;);0)-COINCIDIR("EXP_1";DESREF(DataTable;-1;;1;);0)) -----> in english: =OFFSET(DataTable,,MATCH("EXP_1",OFFSET(DataTable,-1,,1,),0)-1,,MATCH("BOOK_1",OFFSET(DataTable,-1,,1,),0)-MATCH("EXP_1",OFFSET(DataTable,-1,,1,),0))

Output layout (worksheet Sheet2):

A1: =Sheet1!A1

B1: =IZQUIERDA(INDICE(TransposeTitleList;1);3) -----> in english: =LEFT(INDEX(TransposeTitleList,1),3)
(adjust the 3 properly)

C1: =SI(COLUMNA()-2<=MAX(HelperCountList);INDICE(DataTable;COINCIDIR(COLUMNA()-2;HelperCountList;0);2);"") -----> in english: =IF(COLUMN()-2<=MAX(HelperCountList),INDEX(DataTable,MATCH(COLUMN()-2,HelperCountList,0),2),"")
(copy across as required, at least up to the max occurrence of the filter string used, in the example 3 columns more, G1)

A2: =SI(FILA()-1<=COLUMNAS(TransposeTable);INDICE(DataTable;COINCIDIR(ENTERO((FILA()-1+COLUMNAS(TransposeTable)-1)/COLUMNAS(TransposeTable));HelperCountList;0);1);"") -----> in english: =IF(ROW()-1<=COLUMNS(TransposeTable),INDEX(DataTable,MATCH(INT((ROW()-1+COLUMNS(TransposeTable)-1)/COLUMNS(TransposeTable)),HelperCountList,0),1),"")
(copy down as required, at least up to the columns no. of TransposeTable, in the example 3 rows more, A11)

B2: =SI(FILA()-1<=COLUMNAS(TransposeTable);INDICE(TransposeTitleList;1;RESIDUO(FILA()-1+COLUMNAS(TransposeTable)-1;COLUMNAS(TransposeTable))+1);"") -----> in english: =IF(ROW()-1<=COLUMNS(TransposeTable),INDEX(TransposeTitleList,1,MOD(ROW()-1+COLUMNS(TransposeTable)-1,COLUMNS(TransposeTable))+1),"")
(idem A2)

C2: =SI(Y(FILA()-1<=COLUMNAS(TransposeTable);COLUMNA()-2<=MAX(HelperCountList));INDICE(TransposeTable;COINCIDIR(COLUMNA()-2;HelperCountList;0);RESIDUO(FILA()-1+COLUMNAS(TransposeTable)-1;COLUMNAS(TransposeTable))+1);"") -----> in english: =IF(AND(ROW()-1<=COLUMNS(TransposeTable),COLUMN()-2<=MAX(HelperCountList)),INDEX(TransposeTable,MATCH(COLUMN()-2,HelperCountList,0),MOD(ROW()-1+COLUMNS(TransposeTable)-1,COLUMNS(TransposeTable))+1),"")
(copy across and down as required, or up to the extents used for C1 across and A2 down, in the example thru G11)

Just advise if any issue.

Regards!

PS: Please be as kind as to replace the ;) by "; )" unquoted and without the embedded space; it matches a smile definition which interferes with cell referencing.
 

Attachments

  • transpose values based on select criteria - example (for mlttkw at chandoo.org).xlsx
    12.7 KB · Views: 16
ABSOLUTELY FANTASTIC!!!!!!!! Thank you so much...one day I hope to pass this knowledge on...

thanks again
 
Hi, mlttkw!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
this scenarioworks great!!! now i have one last question. Using my input example where there are 3 Scenarios..."SPSBASE" (which you solved for) "SPSADV" and "SPSEVADV"....how can i use the input as is and put each individual scenario transposed onto indivual tabs in the workbook. In addition to the one tab where SPSBASE is the input could i also create SPSADV and SPSEVADV respectivey on subsequent tabs....with the current setup there is only one value for the FILTERCELL =SPSBASE.

Hopefully this makes sense ...let me know if you need any additional info for clarity
 
Hi, mlttkw!

Yes, it makes sense. No, I don't need any additional info for clarity. It's you who need to do a lot of work and try to adapt the solution for 1 worksheet with 1 filter criteria to N worksheets with N filter criteria.

If I were you, I'd start moving the named range FilterCell B19 from source worksheet to output worksheet. Normally a cut & paste will do the job. But that won't work, since the idea is to transform it from a workbook scope name to a worksheet scope name. And Name Manager doesn't allow you to do this.

So you'll have to delete it and create again, setting worksheet scope (enabled at creation time only). Then check if the output works as expected. If so, you should duplicate (copy) the output worksheet, check that the FilterCell name continues being worksheet scoped, change the value and try. And so on.

If you add the FilterCell cell before the actual output columns, i.e. in A:B columns, you should adjust the:
COLUMN()-2
to:
COLUMN()-4
in the formulas of actual columns C:G, E:F new ones.

But at last it won't work neither. The helper column added in the source worksheet refers to the named range FilterCell... so it'd be an ambiguous reference if the name has worksheet scope. Well, I leave you with the tests, even if I think you won't succeed. If something new crosses my mind I'll get back to you.

Regards!
 
LOL!!! I was afraid of such an answer :) .....i'm banging my head trying different options to no avail...what if i don't need a helper column -- my data example is truly not representative -- ie. SPSBASE, SPSADV, SPSSEVADV will occur 13 times each....

thanks again
 
Hi, mlttkw!
Never ask a question for which answer you're not prepared. In the solution for the posted model, almost all relies on the helper column. Maybe with another model you might get rid of it, but it'd be a different solution.
Regards!
 
Hi, mlttkw!
A quick & dirty alternative maybe is moving the helper column to each output worksheet, so using a named range with worksheet scope shouldn't cause any trouble. Do you think you can manage to handle it? Most of the stuff is explained in my penultimate post.
Regards!
 
LOL!!! I am quite lost to be honest..this hurts my small little brain...how about a different approach...
the dynamic data source will have the same distinct Names in column a and b and c and the same of occurrences in each column. Across row 1 the values are also static.
the data within will change

in my output I need to match the distinct values from the input to the output and transpose the values accordingly to the output file

I am attaching my example for better depiction...I am playing around with a combination of Match, Index and Transpose and can't quite get the outcome. I am close using the following for my example attached =INDEX($D$5:$F$13,MATCH(1,(C$17=$A$5:$A$13)*(C$18=$B$5:$B$13)*(C$19=$C$5:$C$13),0),MATCH($A20,$D$4:$F$4,0))
 

Attachments

  • Book3.xlsx
    11.9 KB · Views: 9
just wanted to say thank you again for your help...I think I found the answer to my problem

=SUMIFS(INDEX($D$5:$F$13,0,MATCH($A20,$D$4:$F$4,0)),$A$5:$A$13,C$17,$B$5:$B$13,C$18,$C$5:$C$13,C$19)
 
Hi, mlttkw!
Glad you could a workaround. I didn't test it but if you say it works, why should I doubt? And even more on a Friday evening... ;)
Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Hi, mlttkw!

This issue kept playing circles around my head, so in an inspiration rapt I decided to reopen the workbook, save it as a macro enabled .xlsm file, delete the named formula FilterXCell workbook scoped, deleted the FilterCell named ranges worksheet scoped, add a explicitly volatile function FilterCellX, place a call to it in A2 of each child worksheet, and make that cell get recalculated in the worksheet activate event of the workbook.

Main worksheet Sheet1 (replace FilterXCell by FilterCellX()):
R2: =CONTAR.SI(A$2:A2;FilterCellX()) -----> in english: =COUNTIF(A$2:A2,FilterCellX())

Child worksheets
A2: =FilterCellX()

New standard module:
Code: 
Option Explicit

Public Function FilterCellX()
    ' constants
    Const ksRng = "B1"
    ' start
    Application.Volatile
    ' process
    FilterCellX = ActiveSheet.Range(ksRng).Value
    ' end
End Function

Workbook class module:
Code: 
Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ' constants
    Const ksWSMain = "Sheet1"
    Const ksRange = "B1"
    ' declarations
    ' start
    If ActiveSheet.Name = ksWSMain Then Exit Sub
    ' process
    ActiveSheet.Range(ksRange).Calculate
    ' end
End Sub

So unless I missed something, I think we won! :)

Hope you come back to check it.

Regards!
 

Attachments

  • transpose values based on select criteria - example 2 (for mlttkw at chandoo.org).xlsm
    23.6 KB · Views: 6
Last edited:
You must log in or register to reply here.
Back
Top