Pulling specific columns by transpose two headers

[deniztopcu]

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]

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
)

Book17
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Symbol	ItemCode	2022/9	2022/6	2022/3	2021/12	2021/9	2021/6			1A	1A	1A	1A	1A	1AA	1AA	1AA	1AA	1AA	1AB	1AB	1AB	1AB	1AB	1AE	1AE	1AE	1AE	1AE
2	a	1A	62	88	97	47	78	10			2022/9	2022/6	2022/3	2021/12	2021/9	2022/9	2022/6	2022/3	2021/12	2021/9	2022/9	2022/6	2022/3	2021/12	2021/9	2022/9	2022/6	2022/3	2021/12	2021/9
3	a	1AA	62	37	85	24	57	20		a	62	88	97	47	78	62	37	85	24	57	27	74	88	51	41	99	41	74	68	22
4	a	1AB	27	74	88	51	41	30		b	20	81	18	98	5	50	74	41	45	97	77	3	96	53	20	0	0	0	0	0
5	b	1A	20	81	18	98	5	40		c	0	0	0	0	0	63	80	43	48	75	0	0	0	0	0	0	0	0	0	0
6	c	1AA	63	80	43	48	75	50
7	b	1AB	77	3	96	53	20	60
8	b	1AA	50	74	41	45	97	70
9	a	1AE	99	41	74	68	22	80
Sheet1

Cell Formulas
Range	Formula
J1:AD5	J1	=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]

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]

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]

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]

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]

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]

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]

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

thanks @smozgur

 

[smozgur]

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 )