Consolidating data from multiple columns

Copper Contributor
Hi! I am working with a report in excel that contains several possible columns that indicate "project name". The challenge is, there are 6 columns to choose from, but in each row, only 1 column will have the project name. I want to know how to look at each of those 6 columns, ignore any blank ones, and use only the value of the column that has the project name for that row. The result I need is to have a single column for "project name" and not 6. Thanks!
7 Replies

@Rustynail You could create one extra column that concatenates the six columns that may contain a project name, for instance by using:

=TEXTJOIN(,TRUE,A2:F2)

and copy it down. In this example, the six project name columns are a A to F. If they are not in adjacent columns, you can replace A2:F2 by references to each individual cell in the row, let's say B2,E2,H2,J2,M2,N2

 

A variant to the above you could use CONCATENATE, like this:

=CONCATENATE(A2,B2,C2,D2,E2,F2)

 

Please edit your post & share some sample data with us along with the expected output, will help us to first understand the issue to solve !!

@Riny_van_Eekelen 

Please share some sample data along with your post ,, to justify that the project name are only in A2, B2, C2, E2 & F2 !!

 

May possible are in random order,, or possibly the OP wants summary for each project name since OP is looking for CONSOLIDATION!!

 

Please be specific & loud while responding the post ,,,, just putting ??? mark not enough to understand your concern !!

@Rajesh_Sinha Not having any concerns. Just don't understand your comment. Hence "???".

I have tried to raised the valid point,,, that being a solution provider one has to share the used data also,, so the readers as well OP can justify the method and believe that it works,,,, since @Rustynail has not shared any sample data so in this situation the formula shown by you is only based on assumption !!

 

Personally,, I do believe that DATA CONSOLIDATION is the core issue,, and the shown method doesn't addressing it !