CODEWITHSUNDEEP
excelexcel-formula
Farm3r
Farm3r

Reputation: 1

Excel formula to display what data elements are present, is concatenate the right approach?

I have an excel file with 12K+ names with various columns and want to be able to see which data elements are "true" for each name.

Example file

Last Name First Name DOB XYZ(not required) EmployeeID SSN Financial Account ID Address
Doe John Y Y Y
Smith John Y Y Y
Smith Jayne Y Y
Rock Dwayne Y Y Y
Foster Jane Y

I want to be able to create a column that outputs what data elements are present, like so:

Last Name First Name Data Element DOB XYZ(not required) EmployeeID SSN Financial Account ID Address
Doe John DOB, SSN, Address Y Y Y
Smith John EmployeeID, SSN, Address Y Y Y
Smith Jayne DOB, Financial Account ID Y Y
Rock Dwayne EmployeeID, Financial Account ID, Address Y Y Y
Foster Jane EmployeeID Y

What formula could I use to list out the true data elements? Could I use a concatenate to display only the top level value of column, as that function will skip fields that are blank..

Upvotes: 0

Views: 27

Answers (1)

norie
norie

Reputation: 9857

If you have the FILTER and TEXTJOIN functions available you could use a formula like this.

This formula assumes original data in A1:G6, and the formula is put in H2 and copied down.

=TEXTJOIN(", ",TRUE, FILTER($C$1:$G$1, C2:G2="Y"))

Those functions are available in Excel 365 and later versions of Excel

Upvotes: 0

Related Questions