INDEX MATCH Non-Blank Cell

[redhots4]

I have a list of names where each name is repeated multiple times, with values in only certain columns to the right. An example is shown below. I want to use a formula (INDEX, MATCH?) To return a clean data set without duplicates as shown in the second table. Any help?

Starting Point:

Name	Age	Height	Weight
John Smith	32
John Smith		61
John Smith			185
Amy Miller	35
Amy Miller		56
Amy Miller			115

Desired End Point:

Name	Age	Height	Weight
John Smith	32	61	185
Amy MIller	35	56	115

 

[Joe4]

If each value (Age, Height, and Weight) only appears once per person, why not just use Subtotals, grouping on the name field?
Or perhaps a Pivot Table?

 

[ExceLoki]

as long as the columns stay in the same order, this should work for you
--------------------

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Age	Height	Weight				Age	Height	Weight
2	John Smith	32					John Smith	32	61	185
3	John Smith		61				Amy Miller	61	185	35
4	John Smith			185
5	Amy Miller	35
6	Amy Miller		56
7	Amy Miller			115
Sheet1

Cell Formulas
Range	Formula
H2:J3	H2	=TOROW(B2:D4,1)
Dynamic array formulas.

 

[Arts]

Perhaps this, the below gets the data organised and strips out the blanks

=FILTER(B2:D7&"",(B2:B7<>0))&FILTER(B2:D7&"",(C2:C7<>0))&FILTER(B2:D7&"",(D2:D7<>0))

@Joe4 Is there a way to use the FILTER function to achieve what I have above by not repeating the FILTER function using & ?

I tried =FILTER(B2:D7&"",(B2:B7<>0)+(C2:C7<>0)+(D2:D7<>0)) but this returned the data in its original format, though this would strip out the blanks...

 

[Alex Blakenburg]

I am sure someone can make it fully spill but in the meantime:

Book6
	A	B	C	D	E	F	G	H	I	J
1	Name	Age	Height	Weight			Name	Age	Height	Weight
2	John Smith	32					John Smith	32	61	185
3	John Smith		61				Amy Miller	35	56	115
4	John Smith			185
5	Amy Miller	35
6	Amy Miller		56
7	Amy Miller			115
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=UNIQUE($A$2:$A$7)
H2:J3	H2	=BYCOL(FILTER($B$2:$D$7,$A$2:$A$7=G2,""),LAMBDA(cols,SUM(cols*(--cols<>""))))
Dynamic array formulas.

 

[Fluff]

What version of Excel are you actually using? Your profile shows 2016 which does not have the filter function.

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

 

[Arts]

Apologies, I didn't think it was possible to select two versions, I have 2016 at home, but where I am currently working the client has 365.

Profile updated

 

[ExceLoki]

multiple posts solutions-v3.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Age	Height	Weight		John Smith	32	61	185
2	John Smith	32				Amy Miller	35	56	115
3	John Smith		61
4	John Smith			185
5	Amy Miller	35
6	Amy Miller		56
7	Amy Miller			115
match non-blank cells

Cell Formulas
Range	Formula
F1:F2	F1	=UNIQUE($A$2:$A$7)
G1:I2	G1	=WRAPROWS(TOROW(FILTER($B$2:$D$7,$A$2:$A$7=F1,""),1),COUNTA($B$1:$D$1))
Dynamic array formulas.