Dynamic list created from table if cell in column is not empty

[mattadams84]

Is it possible in excel to create a list of items from a table into a new table if there is data in a column?

For example:

In column a I have data from rows 2 to 50
In column b there is data from rows 2 to 50
In column c there is data only in some rows (random)

i want to create a new dynamic list (with the data) that contains only the items that contain data in column c.

if in the future data is added in to column c that didn't previously have any, then the new list needs to be updated automatically.

is this possible using formula?

 

[Peter_SSs]

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’)

It depends on your version, hence the suggestion above, and also on whether the 'table' is a formal Excel table (ListObject) or simply a section of your worksheet.

However, here are a couple of possibilities. You may need something different again depending on your particular specific circumstances.

For a formal Excel table and a version with the FILTER function:

mattadams84.xlsm
	A	B	C	D	E	F	G	H
1	Hdr1	Hdr2	Hdr3			Hdr1	Hdr2	Hdr3
2	a	1	x			a	1	x
3	b	2				c	3	x
4	c	3	x			d	4	x
5	d	4	x
6	e	5
7
8
Sheet1

Cell Formulas
Range	Formula
F1:H4	F1	=FILTER(Table1[#All],Table1[[#All],[Hdr3]]<>"")
Dynamic array formulas.

For a normal range & no FILTER function:

mattadams84.xlsm
	A	B	C	D	E	F	G	H
1	Hdr1	Hdr2	Hdr3			Hdr1	Hdr2	Hdr3
2	a	1	x			a	1	x
3	b	2				c	3	x
4	c	3	x			d	4	x
5	d	4	x
6	e	5
7
8
Sheet2

Cell Formulas
Range	Formula
F1:H6	F1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$10)/($C$1:$C$10<>""),ROWS(F$1:F1))),"")

 

[mattadams84]

Many thanks,

I had to go the second route, even though my data is in an 'excel' table, I don't have the filter function

 

[Peter_SSs]

I don't have the filter function

All the more reason that it would be a good idea to act on the first paragraph in post #2!

I had to go the second route, even though my data is in an 'excel' table,

If you wanted to adapt the formula to use the formal table structure you could also consider this.

Headers entered manually then F2 formula copied (after adjusting for your actual table name) across and down as far as you might ever need.

mattadams84.xlsm
	A	B	C	D	E	F	G	H
1	Hdr1	Hdr2	Hdr3			Hdr1	Hdr2	Hdr3
2	a	1	x			a	1	x
3	b	2				c	3	x
4	c	3	x			d	4	x
5	d	4	x
6	e	5
7
8
Sheet3

Cell Formulas
Range	Formula
F2:F6	F2	=IFERROR(INDEX(Table13[Hdr1],AGGREGATE(15,6,(ROW(Table13[Hdr1])-ROW(Table13[#Headers]))/(Table13[[Hdr3]:[Hdr3]]<>""),ROWS(F$2:F2))),"")
G2:G6	G2	=IFERROR(INDEX(Table13[Hdr2],AGGREGATE(15,6,(ROW(Table13[Hdr2])-ROW(Table13[#Headers]))/(Table13[[Hdr3]:[Hdr3]]<>""),ROWS(G$2:G2))),"")
H2:H6	H2	=IFERROR(INDEX(Table13[Hdr3],AGGREGATE(15,6,(ROW(Table13[Hdr3])-ROW(Table13[#Headers]))/(Table13[[Hdr3]:[Hdr3]]<>""),ROWS(H$2:H2))),"")