# Pulling specific columns by transpose two headers



## deniztopcu (Jan 1, 2023)

I want to pull the last 5 quarter data of "Symbol and itemcode" from the table.
Symbol and item code" must be transpose.

Pulling specific columns by transposing two headers

I want to reach an image like the first picture so that I can compare.


----------



## smozgur (Jan 1, 2023)

Hi,

I think it could be super easy to create the desired result by using a Pivot Table as shown below 
(Please note that I wrote some random values and symbols since I can't copy from the images. Actually, I suggest using XL2BB for posting data, so helpers can work on the original data by copying it into a worksheet easily).





Note: I initially built an array formula for this. However, I realized that you are not using Excel 365, so this will probably not help as I am not sure if all functions I used exist in your Excel version but I already completed the formula. So, although I believe the Pivot Table approach will be much better, here is the formula as well (for Excel 365).

The formula:

```
=LET(
    howManyYears, 5,
    symbols, UNIQUE(Combined_tIFinancials[Symbol]),
    itemCodes, TRANSPOSE(UNIQUE(Combined_tIFinancials[ItemCode])),
    years, TAKE(DROP(Combined_tIFinancials[#Headers],,2),,howManyYears),
    itemHeaders, INDEX(itemCodes,,QUOTIENT(SEQUENCE(1,COLUMNS(years)*COLUMNS(itemCodes))-1,howManyYears)+1),
    yearHeaders, INDEX(years,,MOD(SEQUENCE(1,COLUMNS(years)*COLUMNS(itemCodes))-1,COLUMNS(years))+1),
    totals, SUMIFS(INDIRECT("Combined_tIFinancials[" & yearHeaders & "]"),Combined_tIFinancials[Symbol],symbols,Combined_tIFinancials[ItemCode], itemHeaders),
    dataTable, VSTACK(itemHeaders, yearHeaders, totals),
    symbolColumn, VSTACK("","",symbols),
    result, HSTACK(symbolColumn, dataTable),
result
)
```
Book17ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1SymbolItemCode2022/92022/62022/32021/122021/92021/6 1A1A1A1A1A1AA1AA1AA1AA1AA1AB1AB1AB1AB1AB1AE1AE1AE1AE1AE2a1A6288974778102022/92022/62022/32021/122021/92022/92022/62022/32021/122021/92022/92022/62022/32021/122021/92022/92022/62022/32021/122021/93a1AA623785245720a62889747786237852457277488514199417468224a1AB277488514130b2081189855074414597773965320000005b1A20811898540c00000638043487500000000006c1AA6380434875507b1AB773965320608b1AA5074414597709a1AE994174682280Sheet1Cell FormulasRangeFormulaJ1:AD5J1=LET(
    howManyYears, 5,
    symbols, UNIQUE(Combined_tIFinancials[Symbol]),
    itemCodes, TRANSPOSE(UNIQUE(Combined_tIFinancials[ItemCode])),
    years, TAKE(DROP(Combined_tIFinancials[#Headers],,2),,howManyYears),
    itemHeaders, INDEX(itemCodes,,QUOTIENT(SEQUENCE(1,COLUMNS(years)*COLUMNS(itemCodes))-1,howManyYears)+1),
    yearHeaders, INDEX(years,,MOD(SEQUENCE(1,COLUMNS(years)*COLUMNS(itemCodes))-1,COLUMNS(years))+1),
    totals, SUMIFS(INDIRECT("Combined_tIFinancials[" & yearHeaders & "]"),Combined_tIFinancials[Symbol],symbols,Combined_tIFinancials[ItemCode], itemHeaders),
    dataTable, VSTACK(itemHeaders, yearHeaders, totals),
    symbolColumn, VSTACK("","",symbols),
    result, HSTACK(symbolColumn, dataTable),
result
)Dynamic array formulas.


----------



## deniztopcu (Jan 2, 2023)

ADNZ.xlsx -  88 KB

thanks,
I will try what you sent. I will post the results here. You're right, I should have included my original file as well.


----------



## deniztopcu (Jan 2, 2023)

deniztopcu said:


> ADNZ.xlsx -  88 KB
> 
> thanks,
> I will try what you sent. I will post the results here. You're right, I should have included my original file as well.


Formula I am trying to make it compatible with very long Turkish characters. I am still not successful.


----------



## smozgur (Jan 2, 2023)

deniztopcu said:


> Formula I am trying to make it compatible with very long Turkish characters. I am still not successful.


I still suggest the Pivot Table approach.

However, if you have chance to use it in Excel 365, then please let me know, so I can convert it to Turkish version for you a bit later today.


----------



## deniztopcu (Jan 2, 2023)

smozgur said:


> Hala Pivot Tablo yaklaşımını öneriyorum.
> 
> Ancak, Excel 365'te kullanma şansınız varsa, lütfen bana bildirin, bugün biraz sonra sizin için Türkçe sürüme çevirebilirim.


I don't have access to Office 365.
I get the result by doing this project with some functions (vlookup, hlookup, index...). But what I want is to reach the result in a short way like yours (the query can be on every line) or to reach the result quickly in a way that makes the system least tiring.
I will be glad if you translate it into Turkish.
I don't know how to do it with pivot table. Is it more advantageous than the first one you wrote in terms of system and time?


----------



## smozgur (Jan 2, 2023)

deniztopcu said:


> I don't have access to Office 365.



I believe, it is more advantageous creating a Pivot Table for this question. Besides, the function I sent above is for 365 because of some unsupported functions in 2021 (that's why I mentioned "if you have a chance to use 365" even though I noticed your Excel version on your profile.)

Creating a Pivot Table is not complicated, in fact, it is much easier and it can be used to create complicated reports faster.

Select the range (or the top-left cell in the range since the wizard will auto-select the whole range), and click on Insert->Pivot Table.





Select New Worksheet and click OK. A new worksheet will be created and the Pivot Table fields pane will appear on the right side.





In the right pane, drag the Symbol field into the Rows section below, ItemCode into the Columns section, and years (2022/9 to 2021/9) into the Values section. Then select "On for Columns Only" option in the Design->Grand Totals dropdown (this will remove the additional row grand totals columns).
.




Optionally change the column headers and widths as you wish. And you got the report you want. Please note you'll need to refresh the Pivot Table when you change something in the source data.

If you don't think the Pivot Table solution is the better one than do not hesitate to keep asking for the function solution since helpers still can come up with Excel 2021-compatible formulas. Otherwise, the way you do it with multiple formulas is already an option anyway. By the way, no matter which method you will use, you shouldn't notice any performance issues. Although you won't notice that, the fastest one would be the Pivot Table method.


----------



## deniztopcu (Jan 2, 2023)

It is a great loss for me that I have not used a pivot table before.

After duplicating the symbols, I'll have to refresh (as you pointed out) to get them back.
Apart from that, this is the result I want to see visually.

Excel Work is triggered after a change is made in the cell I specified. It would make my job even easier if it was automatic. But I will try to handle this with a macro embedded in a button.

The information you provided was invaluable. Thank you very much for your time and effort.


----------



## deniztopcu (Jan 2, 2023)

Pivot tables or your example with functions will give faster results.

thanks  @smozgur


----------



## smozgur (Jan 2, 2023)

deniztopcu said:


> The information you provided was invaluable. Thank you very much for your time and effort


You’re welcome. Glad to hear it helps.
That would be great if you could mark the post as the solution that helped you to help future readers.

(Birşey değil  )


----------



## deniztopcu (Jan 1, 2023)

I want to pull the last 5 quarter data of "Symbol and itemcode" from the table.
Symbol and item code" must be transpose.

Pulling specific columns by transposing two headers

I want to reach an image like the first picture so that I can compare.


----------



## deniztopcu (Jan 2, 2023)

smozgur said:


> Rica ederim. Yardımcı olduğunuza sevindim.
> Gönderiyi, koruyucu okuyuculara yardımcı olmanıza yardımcı olan çözüm olarak işaretlerseniz bu harika olur.
> 
> (Bir şey değil  )


Thanks
(Çeviri yapmak, sorundan daha çok yoruyor. Tekrar emeklerinizden dolayı teşekkür ederim.)


----------



## smozgur (Jan 2, 2023)

deniztopcu said:


> But I will try to handle this with a macro embedded in a button.


Although the following is not something that I will advise since it will run at every change in the data range cells which will dramatically affect the performance and also reset the "Undo" action, it is possible to kind of automate the Pivot Table refresh by detecting cell value changes in the cells in the `Worksheet_Change()` event procedure of Sheet2 (where the source data table resides).


```
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, ActiveSheet.ListObjects("Combined_tblFinancials").DataBodyRange) Is Nothing Then
        Sheet1.PivotTables(1).PivotCache.Refresh
    End If
End Sub
```

Even I don't suggest doing what I "shown" above, you can still use the one-liner from the same code in a button click event procedure as you mentioned for an on-demand refresh:

```
Sheet1.PivotTables(1).PivotCache.Refresh
```


Note: You might want to uncheck the "Autofit column widths on update" option in any case.






deniztopcu said:


> It is a great loss for me that I have not used a pivot table before.


It is never too late!
(In fact, I also suggest taking a look at Power Query as well - unless you are already working with it. It is a whole different world in Excel if you are struggling with data cleansing and transforming data coming from other sources, like accounting application exports, etc.)



deniztopcu said:


> (Çeviri yapmak, sorundan daha çok yoruyor. Tekrar emeklerinizden dolayı teşekkür ederim.)


You can also use the Questions in Other Languages section in your language and someone who speaks your language will surely help. Yet I suggest sticking here as much as you could to get faster help.

Thanks for marking the solution post as it will help future readers.


----------



## deniztopcu (Jan 2, 2023)

I used to use power query manually myself.
With the support I got from the forum, my queries turned from turtle to rabbit. like a magic wand.

But the most important reason why I search for new methods is to find a "fast and stable" working method...


Thank you very much for the macro codes.thanks @smozgur


----------

