CODEWITHSUNDEEP
excelconcatenationconditional-statements
Arjun
Arjun

Reputation: 21

Conditional concatenate in Excel

I have to concatenate the data in D1 from column A1, B1, C1.

But as per below example if A1 is empty, it should fetch the first not blank data in column A, i.e., A3 and than concatenate D1 = (A3, B1, C1).

Like that for D7 = (A8, B7, C7) and D13 = (A15, B13, C13) so on

For example (underscore indicates empty cell):

Sheet 1 

   A   B   C    D
1   _  22  11       
2   _  32  21       
3   B  56  89       
4   _  65  90
5   C  22  11       
6   _  32  21       
7   _  56  89       
8   D  65  90       
9   _  32  21       
10  E  56  89       
11  _  65  90
12  F  22  11       
13  _  32  21       
14  _  56  89       
15  G  65  90        
.                  
.                  
.

Resulting Sheet 1

    A  B   C   D
1   _  22  11  B2211     
2   _  32  21  B3221     
3   B  56  89  B5689     
4   _  65  90  C6590
5   C  22  11  C2211     
6   _  32  21  D3221     
7   _  56  89  D5689     
8   D  65  90  D6590     
9   _  32  21  E3221     
10  E  56  89  E5689     
11  _  65  90  F6590
12  F  22  11  F2211     
13  _  32  21  G3221     
14  _  56  89  G5689     
15  G  65  90  G6590

So, your thoughts would be appreciated, and your code welcome!

Upvotes: 0

Views: 4332

Answers (2)

davejal
davejal

Reputation: 6133

You could do this very easy.

  1. add the following to cell d1:

    =VLOOKUP("*",A1:$A$15,1,FALSE)&B1&C1

  2. copy it to the other cells by using the fill handle

enter image description here

If you can't remove the underscores try @harun24hr comment, it also works (don't forget to use the fill handle after writing the formula, prior to that it will not output what you want)

Upvotes: 3

pnuts
pnuts

Reputation: 59442

Please try in D1 and copied down to suit:

=IF(ISBLANK(A1),LEFT(D2),A1)&B1&C1

Upvotes: 0

Related Questions