Return values in order across multiple columns with criteria

[Country_Calc_2]

I am looking for a formula that will return values based on the order that they appear in Column A with the requirement that the country is skipped if the Column B value is 0.

This has to be done across multiple columns as in examples in Column D to K - because it is intended to be pasted into Word as a table with least possible vertical space.

Column A	Column B		Column D	Column E	Column F	Column G	Column H	Column I	Column J	Column K
Country	Projects		Country	Projects	Country	Projects	Country	Projects	Country	Projects
Argentina	31		Argentina	31​	Brazil	35​	China	9​	Egypt	2​
Armenia	0		Australia	80​	Bulgaria	30​	Colombia	15​	Estonia	10​
Australia	80		Austria	15​	Canada	107​	Costa Rica	1​	Denmark	24​
Austria	15		Belgium	46​	Chile	18​	Czechia	58​
Belarus	0
Belgium	46
Brazil	35
Bulgaria	30
Canada	107
Chile	18
China	9
Colombia	15
Costa Rica	1
Croatia	0
Czechia	58
Denmark	24
Dominican Republic	0
Egypt	2

 

[Country_Calc_2]

The order was incorrectly typed towards the end in Column J. Just ignore this please.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Country	Projects		Country	Projects	Country	Projects	Country	Projects	Country	Projects
2	Argentina	31		Argentina	31	Australia	80	Austria	15	Belgium	46
3	Armenia	0		Brazil	35	Bulgaria	30	Canada	107	Chile	18
4	Australia	80		China	9	Colombia	15	Costa Rica	1	Czechia	58
5	Austria	15		Denmark	24	Egypt	2
6	Belarus	0
7	Belgium	46
8	Brazil	35
9	Bulgaria	30
10	Canada	107
11	Chile	18
12	China	9
13	Colombia	15
14	Costa Rica	1
15	Croatia	0
16	Czechia	58
17	Denmark	24
18	Dominican Republic	0
19	Egypt	2
Data

Cell Formulas
Range	Formula
D2:K5	D2	=LET(f,FILTER(A2:B100,B2:B100<>0),WRAPROWS(TOCOL(f),CEILING(ROWS(f)/2,2),""))
Dynamic array formulas.

 

[Country_Calc_2]

This sort of works except it puts the countries in order across columns instead of rows.

It also goes across too many columns to be practical for a Word paste.

I should have mentioned that I am limited to 8 columns total (or 4 columns of country names).

This is what it looks like on a larger dataset. I will see if I can tweak your formula.

Argentina

31​

Armenia

3​

Australia

80​

Austria

15​

Belarus

6​

Belgium

46​

Brazil

Estonia

10​

Finland

11​

France

106​

Georgia

28​

Germany

126​

Greece

4​

Guatemala

Lithuania

4​

Macedonia

4​

Malaysia

4​

Mexico

30​

Moldova

4​

Netherlands

82​

New Zealand

Serbia

33​

Singapore

7​

Slovakia

10​

South Africa

16​

Spain

123​

Sweden

31​

Switzerland

 

[Country_Calc_2]

This sort of works except it puts the countries in order across columns instead of rows.

It also goes across too many columns to be practical for a Word paste.

I should have mentioned that I am limited to 8 columns total (or 4 columns of country names).

This is what it looks like on a larger dataset. I will see if I can tweak your formula.

Argentina

31​

Armenia

3​

Australia

80​

Austria

15​

Belarus

6​

Belgium

46​

Brazil

Estonia

10​

Finland

11​

France

106​

Georgia

28​

Germany

126​

Greece

4​

Guatemala

Lithuania

4​

Macedonia

4​

Malaysia

4​

Mexico

30​

Moldova

4​

Netherlands

82​

New Zealand

Serbia

33​

Singapore

7​

Slovakia

10​

South Africa

16​

Spain

123​

Sweden

31​

Switzerland

Changing this part of the formula limits to the 8 columns I was looking for, but I do not understand how to change from columns to rows - "CEILING(ROWS(f)/10,2)"

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Country	Projects		Country	Projects	Country	Projects	Country	Projects	Country	Projects
2	Argentina	31		Argentina	31	Brazil	35	China	9	Denmark	24
3	Armenia	0		Australia	80	Bulgaria	30	Colombia	15	Egypt	2
4	Australia	80		Austria	15	Canada	107	Costa Rica	1
5	Austria	15		Belgium	46	Chile	18	Czechia	58
6	Belarus	0
7	Belgium	46
8	Brazil	35
9	Bulgaria	30
10	Canada	107
11	Chile	18
12	China	9
13	Colombia	15
14	Costa Rica	1
15	Croatia	0
16	Czechia	58
17	Denmark	24
18	Dominican Republic	0
19	Egypt	2
Data

Cell Formulas
Range	Formula
D2:K5	D2	=LET(f,FILTER(A2:B100,B2:B100<>0),r,CEILING(ROWS(f)/4,1),s,SEQUENCE(,8,0),IFERROR(INDEX(f,r*INT(s/2)+SEQUENCE(r),MOD(s,2)+1),""))
Dynamic array formulas.

 

[Country_Calc_2]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Country	Projects		Country	Projects	Country	Projects	Country	Projects	Country	Projects
2	Argentina	31		Argentina	31	Brazil	35	China	9	Denmark	24
3	Armenia	0		Australia	80	Bulgaria	30	Colombia	15	Egypt	2
4	Australia	80		Austria	15	Canada	107	Costa Rica	1
5	Austria	15		Belgium	46	Chile	18	Czechia	58
6	Belarus	0
7	Belgium	46
8	Brazil	35
9	Bulgaria	30
10	Canada	107
11	Chile	18
12	China	9
13	Colombia	15
14	Costa Rica	1
15	Croatia	0
16	Czechia	58
17	Denmark	24
18	Dominican Republic	0
19	Egypt	2
Data

Cell Formulas
Range	Formula
D2:K5	D2	=LET(f,FILTER(A2:B100,B2:B100<>0),r,CEILING(ROWS(f)/4,1),s,SEQUENCE(,8,0),IFERROR(INDEX(f,r*INT(s/2)+SEQUENCE(r),MOD(s,2)+1),""))
Dynamic array formulas.

This works perfectly, thanks so much.

 

[Fluff]

You're welcome & thanks for the feedback.