Dynamically extract data from row based on criteria and fill data contiguously into another row

[khtanned]

Greetings from a total noob.
How can I use a formula (no VBA) to extract data from cells in a row based on criteria and fill the extracted data into another row in the order from left to right
without blanks in between.

In the example shown, the colors Brown to Black in row #1 are the data to be extracted if the corresponding cell in row #2 is not blank ("1" in this case).
The extracted data is to be filled into another row in the order from left to right as shown in row #5. The result must change accordingly when the cells
in row #2 are updated. The formula must still work wherever the result row is. That is to say it could be on a row starting from A5 as shown above or K16 or AD20 etc.

I found some similar questions and suggested solutions on the Internet but all of them have the data arranged downwards in columns. I tried using their formula and
changed the range and Row function to Column function or vice versa to no avail. Any help would be very much appreciated! Thank you.

 

[JamesCanale]

365 makes this really easy. So easy in fact, that I guess you don't have that. I made up something with a few helper rows too (rows 8-10)

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Brown	Red	Orange	Yellow	Green	Blue	Violet	Grey	White	Black
2	1			1	1			1		1
3
4
5	Brown	Yellow	Green	Grey	Black
6
7
8	1	1	1	2	3	3	3	4	4	5
9	1	2	3	4	5	6	7	8	9	10
10	Brown	Yellow	Green	Grey	Black
Sheet24

Cell Formulas
Range	Formula
A5:E5	A5	=FILTER(A1:J1,A2:J2=1)
A8:J8	A8	=SUM($A2:A2)
A10:J10	A10	=IFNA(INDEX($A$1:$J$1,1,MATCH(A9,$A$8:$J$8,0)),"")
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[khtanned]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm still using version 2003. Forgot to mention that in my post. Thank you very much!!

 

[Peter_SSs]

Try this. Note that the formula must be confirmed with Ctrl+Shift+Enter, not just Enter before copying across.

22 09 10.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Brown	Red	Orange	Yellow	Green	Blue	Violet	Grey	White	Black
2	1			1	1			1		1
3
4
5	Brown	Yellow	Green	Grey	Black
No blanks

Cell Formulas
Range	Formula
A5:J5	A5	=IFERROR(INDEX($A1:$J1,SMALL(IF($A2:$J2=1,COLUMN($A1:$J1)-COLUMN($A1)+1),COLUMNS($A:A))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[khtanned]

365 makes this really easy. So easy in fact, that I guess you don't have that. I made up something with a few helper rows too (rows 8-10)

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Brown	Red	Orange	Yellow	Green	Blue	Violet	Grey	White	Black
2	1			1	1			1		1
3
4
5	Brown	Yellow	Green	Grey	Black
6
7
8	1	1	1	2	3	3	3	4	4	5
9	1	2	3	4	5	6	7	8	9	10
10	Brown	Yellow	Green	Grey	Black
Sheet24

Cell Formulas
Range	Formula
A5:E5	A5	=FILTER(A1:J1,A2:J2=1)
A8:J8	A8	=SUM($A2:A2)
A10:J10	A10	=IFNA(INDEX($A$1:$J$1,1,MATCH(A9,$A$8:$J$8,0)),"")
Dynamic array formulas.

Hi James,
Thank you very much for your help. My apologies for not mentioning earlier that I am using Excel 2003 at home. I couldn't enter the filter function.

As mentioned in my earlier post, I saw this article on "How to extract a dynamic list from a data range based on a criteria without filters in excel" in the link below.

How To Extract A Dynamic List From A Data Range Based On A Criteria Without Filters In Excel

In this tutorial we are going to see how we can use an array formula using IFERROR, INDEX, SMALL, IF and ROW functions to create a dynamic, filtered list without using any built-in Excel functions like Filter to accomplish this.

excel-bytes.com

​

But I could not get it to work.

You mentioned that it would be an easy task with Excel 365. We are using Excel 365 in the office. How will it be done with Excel 365?

By the way, I just tried Peter_SSs's formula and it works though I had to change the IFERROR function to IF(ISERROR... for my version of Excel.

I'd still like to see your solution with Excel 365 version.

Thank you very much, again! I really appreciate it!

 

[khtanned]

Try this. Note that the formula must be confirmed with Ctrl+Shift+Enter, not just Enter before copying across.

22 09 10.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Brown	Red	Orange	Yellow	Green	Blue	Violet	Grey	White	Black
2	1			1	1			1		1
3
4
5	Brown	Yellow	Green	Grey	Black
No blanks

Cell Formulas
Range	Formula
A5:J5	A5	=IFERROR(INDEX($A1:$J1,SMALL(IF($A2:$J2=1,COLUMN($A1:$J1)-COLUMN($A1)+1),COLUMNS($A:A))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Peter,

I just tried your formula and it works!!! I googled and found that the IFERROR function is IF(ISERROR(... for my version of Excel (2003). Anyway I changed that and it worked.
I have yet to copy the formula into many rows in the actual worksheet. Hope it will work just fine as well.

Thank you very much for your help!! Really appreciate it!!

 

[JamesCanale]

My 365 solution is in row 5 of my response.

 

[Peter_SSs]

IFERROR function is IF(ISERROR(... for my version of Excel (2003).

Ah, yes, I guess that is so. Hard to remember just what was available back 19 years!!

 

[khtanned]

My 365 solution is in row 5 of my response.

Hi James,

Sorry I missed that. Yes, I see it now. Thanks again!!