Wraprows for Excel 2019 version

[ExcelNewbie2020]

For Excel version 2019


i have this table.. my formula returns horizontally the same as source data. i would like to arrange it vertically by group of 3. would it be possible using excel 2019?

Excel.xlsm
	A	B	C	D	E	F	G
1	Nickname	Amount1	OR No.1	Date1	Amount2	OR No.2	Date2
2	Chile	36.97	1	11-Jul-2024	464	14	16-Oct-2024
3	Rwanda	72.84	2	2-Oct-2024	72.84	2	2-Oct-2024
4	Ethiopia	21.22	3	25-Feb-2024	21.22	3	25-Feb-2024
5	Zimbabwe	6.47	4	27-Jun-2024	6.47	4	27-Jun-2024
6	Uganda	66.79	5	23-Nov-2024	66.79	5	23-Nov-2024
7	Uzbekistan	42.87	6	9-May-2024	42.87	6	9-May-2024
8	Sudan	62.34	7	24-Jun-2024	62.34	7	24-Jun-2024
9	Tonga	54.11	8	17-May-2024	54.11	8	17-May-2024
10	Poland	62.84	9	14-Dec-2024	62.84	9	14-Dec-2024
11	Panama	66.38	10	30-Nov-2024	66.38	10	30-Nov-2024
12
13
14
15
16
17
18		SEARCH	CHILE
19		CURRENT FORMULA RESULT
20		Amount1	OR No.1	Date1	Amount2	OR No.2	Date2
21		36.97	1	11-07-24	464	14	16-10-24
22
23
24
25
26		EXPECTED RESULT
27		Amount	OR	DATE
28		36.97	1	11-07-24
29		464	14	16-10-24
30
31
32
AGRESEARCH (2)

Cell Formulas
Range	Formula
B21:G21	B21	=IFERROR(INDEX($B$2:$G$14,AGGREGATE(15,6,(ROW($B$2:$B$14)-ROW($B$2)+1)/(ISNUMBER(SEARCH($C$18,$A$2:$A$14)) ),ROWS($B$21:B21)),COLUMNS($B$21:B21)),"")

 

[JEC]

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = {"Amount","OR","DATE"},
    toList = Table.ToColumns(Table.RemoveColumns(Source,{"Nickname"})),
    comb = List.Accumulate({{0,3},{3,3}},#table({}, {}),(s,c)=> Table.Combine({s, Table.FromColumns(List.Range(toList,c{0},c{1}),cols)})),
    result = Table.Sort(comb,{{"OR", Order.Ascending}})
in
    result

 

[JEC]

Here another code with less custom functions

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    t1 = Table.SelectColumns(Source,{"Amount1", "OR No.1", "Date1"}),
    t2 = Table.RenameColumns(Table.SelectColumns(Source,{"Amount2", "OR No.2", "Date2"}),{{"Amount2", "Amount1"}, {"OR No.2", "OR No.1"}, {"Date2", "Date1"}}),
    result = Table.Sort(Table.Combine({t1,t2}),{{"OR No.1", Order.Ascending}})
in
    result

 

[ExcelNewbie2020]

Here another code with less custom functions

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    t1 = Table.SelectColumns(Source,{"Amount1", "OR No.1", "Date1"}),
    t2 = Table.RenameColumns(Table.SelectColumns(Source,{"Amount2", "OR No.2", "Date2"}),{{"Amount2", "Amount1"}, {"OR No.2", "OR No.1"}, {"Date2", "Date1"}}),
    result = Table.Sort(Table.Combine({t1,t2}),{{"OR No.1", Order.Ascending}})
in
    result

thanks man, i am looking for a modified version of my current formula that will give the expected output..

 

[JEC]

I don't have xl2bb here, so:

Put the headers exactly in the same cells as I did. --> B14 : D14
then put this formula in B15 and pull down and right.

=INDEX($B$2:$G$11,INT((ROW(A1)-1)/2)+1,MOD(COLUMN(A1)-1,3)+IF(MOD(COUNTA($B$14:$B14),2),1,4))

 

[ExcelNewbie2020]

I don't have xl2bb here, so:

Put the headers exactly in the same cells as I did. --> B14 : D14
then put this formula in B15 and pull down and right.

=INDEX($B$2:$G$11,INT((ROW(A1)-1)/2)+1,MOD(COLUMN(A1)-1,3)+IF(MOD(COUNTA($B$14:$B14),2),1,4))

View attachment 111944

thanks, maybe you overlook on my current formula, there is a search criteria at C18..Like for example, if i search for CHILE the result will be thes

 

[JEC]

Yes I missed that

=INDEX($B$2:$G$11,MATCH($B$13,$A$2:$A$11),(COLUMN(A1)-1)+IF(MOD(COUNTA($B$14:$B14),2),1,4))

 

[ExcelNewbie2020]

Yes I missed that

=INDEX($B$2:$G$11,MATCH($B$13,$A$2:$A$11),(COLUMN(A1)-1)+IF(MOD(COUNTA($B$14:$B14),2),1,4))

View attachment 111947

thanks man, it did work.. however, i forgot to mention that my data has morethan 6 column wide index range. i have a total of 30 column wide index range and its values is regularly updated.
i think the formula is designed only for a 6column wide range?