Consolidating data from multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2157943%22%20slang%3D%22en-US%22%3EConsolidating%20data%20from%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2157943%22%20slang%3D%22en-US%22%3EHi!%20I%20am%20working%20with%20a%20report%20in%20excel%20that%20contains%20several%20possible%20columns%20that%20indicate%20%22project%20name%22.%20The%20challenge%20is%2C%20there%20are%206%20columns%20to%20choose%20from%2C%20but%20in%20each%20row%2C%20only%201%20column%20will%20have%20the%20project%20name.%20I%20want%20to%20know%20how%20to%20look%20at%20each%20of%20those%206%20columns%2C%20ignore%20any%20blank%20ones%2C%20and%20use%20only%20the%20value%20of%20the%20column%20that%20has%20the%20project%20name%20for%20that%20row.%20The%20result%20I%20need%20is%20to%20have%20a%20single%20column%20for%20%22project%20name%22%20and%20not%206.%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2157943%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2158010%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F975490%22%20target%3D%22_blank%22%3E%40Rustynail%3C%2FA%3E%26nbsp%3BYou%20could%20create%20one%20extra%20column%20that%20concatenates%20the%20six%20columns%20that%20may%20contain%20a%20project%20name%2C%20for%20instance%20by%20using%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%2CTRUE%2CA2%3AF2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20copy%20it%20down.%20In%20this%20example%2C%20the%20six%20project%20name%20columns%20are%20a%20A%20to%20F.%20If%20they%20are%20not%20in%20adjacent%20columns%2C%20you%20can%20replace%20%3CSTRONG%3EA2%3AF2%3C%2FSTRONG%3E%20by%20references%20to%20each%20individual%20cell%20in%20the%20row%2C%20let's%20say%20%3CSTRONG%3EB2%2CE2%2CH2%2CJ2%2CM2%2CN2%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20variant%20to%20the%20above%20you%20could%20use%20CONCATENATE%2C%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCONCATENATE(A2%2CB2%2CC2%2CD2%2CE2%2CF2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2158013%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20data%20from%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158013%22%20slang%3D%22en-US%22%3EPlease%20edit%20your%20post%20%26amp%3B%20share%20some%20sample%20data%20with%20us%20along%20with%20the%20expected%20output%2C%20will%20help%20us%20to%20first%20understand%20the%20issue%20to%20solve%20!!%3C%2FLINGO-BODY%3E
Occasional Visitor
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-S 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 !