excel formula joining non-blank cells from 2 columns

[Sampers25]

Hello,

I have a large excel file on which some date wise(column) followup has been documented but now we are thinking to have entire data(non-blank) date-wise joined in one cell.

E.g.

Name 17/5 18/5 19/5 20/5 21/5 22/5 23/5
abhishek NA Busy Call tomo NA
rani Busy Busy NA NA
kajol NA Busy Secy detail
ajay NA NA travelling
amir NA Later Script
salman Busy NA NA Took details will callback


I wanted to have one row which should give me status like
Name Status 17/5 18/5 19/5 20/5 21/5 22/5 23/5
abhishek 17/5 NA;19/5 busy etc NA Busy Call tomo NA
rani 18/5 Busy;19/5 busy etc Busy Busy NA NA
kajol 17/5 NA; 20/5 Busy etcNA Busy Secy detail
ajay 17/5 NA; 19/5 NA;21/5NA NA travelling
amir 17/5 NA; 19/5 Later.. NA Later Script
salman 17/5 Busy; 18/5 NA.. Busy NA NA Took details

 

[Shteven]

Please upload a sample workbook so we can see the structure of your data. Also include your desired output. Thanks.

 

[Sampers25]

Thanks Shteven. However due to company IT restrictions, i am unable to attach file here.

However, below is my understanding regarding output field.
This field shall check corresponding row entries and ignore blank one and one which has data, it should pick date (Top field) of that entry & then respective comment & next with separation indicator as "; ".

Basically ignoring blank entries in a row, if some data then concatenation of Top row & data and look for next data

E.g.
17 May VM; 18 May NA; 20 May Secy says travelling; and so on...

 

[Sampers25]

Something like this -
after inserting formula of checking not blank rows - =$C$1&" "&C2&"; "&$E$1&" "&E2&"; "&$G$1&" "&G2&"; "&$I$1&" "&I2&"; "

 

[Shteven]

This is what I came up with if your table starts in A1:

=$A2&" "&IF(B2<>"",B$1&" "&B2&"; ","")&IF(C2<>"",C$1&" "&C2&"; ","")&IF(D2<>"",D$1&" "&D2&"; ","")&IF(E2<>"",E$1&" "&E2&"; ","")&IF(F2<>"",F$1&" "&F2&"; ","")&IF(G2<>"",G$1&" "&G2&"; ","")&IF(H2<>"",H$1&" "&H2&"; ","")

I'd be interested to know if it's possible to use an array to solve this question since you're repeating the same test and giving the same output for each cell. Maybe a ninja will read this and comment.

 

[Somendra Misra]

Hi,

Will VBA be acceptable? If so than kindly, upload a sample (may be small) file with say 10 rows of i/p and expected o/p data.

Regards,

 

[Sampers25]

Thanks Shteven & Som, for response.

Shteven - formula seems deliver result like
42507 NA; 42509 Busy; 42511 Call tomo; 42513 NA ;
Also, it has a limitation of me adding column nos. everytime i add some comment and it does not auto-increment.

Som - As i mentioned due to IT restrictions, wouldn't be possible for me to attach anything. Also, rather than VBA, if we can have a formula with auto increment feature and with condition of non-blank and if condition fulfills, i.e., if B2 is non-blank then B1 & B2 and again check next if some data and then C1 & C2 or check next.

 

[Shteven]

Shteven - formula seems deliver result like
42507 NA; 42509 Busy; 42511 Call tomo; 42513 NA ;
Also, it has a limitation of me adding column nos. everytime i add some comment and it does not auto-increment.

Your original requirements did not ask for either of these conditions. In the future, please make sure to state all of your requirements when you ask a question; otherwise, you will not get the answer you are looking for.

Som - As i mentioned due to IT restrictions, wouldn't be possible for me to attach anything.

If you cannot attach a file with actual company data in it, please create a file with dummy data (random letters, numbers, etc) and expected results based on that.

 

[Somendra Misra]

Thanks Shteven & Som, for response.

Shteven - formula seems deliver result like
42507 NA; 42509 Busy; 42511 Call tomo; 42513 NA ;
Also, it has a limitation of me adding column nos. everytime i add some comment and it does not auto-increment.

Som - As i mentioned due to IT restrictions, wouldn't be possible for me to attach anything. Also, rather than VBA, if we can have a formula with auto increment feature and with condition of non-blank and if condition fulfills, i.e., if B2 is non-blank then B1 & B2 and again check next if some data and then C1 & C2 or check next.

Hi,

This looks like concatenation problem. The native formula for concatenation in Excel does not deal with array, so I doubt an auto increment formula will not work.

I think Excel 2016 has this function than we need some sample data, may be you can create an excel file back in home, just dummy data keeping the structure same, than we can do some R&D on that .

Regards,

 

[Sampers25]

Hi,

This looks like concatenation problem. The native formula for concatenation in Excel does not deal with array, so I doubt an auto increment formula will not work.

I think Excel 2016 has this function than we need some sample data, may be you can create an excel file back in home, just dummy data keeping the structure same, than we can do some R&D on that .

Regards,

Thanks Som. I will attach excel with dummy data from home. Appreciate your response.

 

[Sampers25]

Your original requirements did not ask for either of these conditions. In the future, please make sure to state all of your requirements when you ask a question; otherwise, you will not get the answer you are looking for.




If you cannot attach a file with actual company data in it, please create a file with dummy data (random letters, numbers, etc) and expected results based on that.

I think Shteven you should read the subject of this thread and my initial question more clearly which always stated of condition i.e., being non-blank. Auto-increment is just a additional facility if i can get in a formula otherwise, its fine. Unfortunately, your formula does not meet the basic requirement i.e. checking cell being non-blank. Anyways, thanks for your comment.

 

[Shteven]

To clarify, my formula does check each cell for non-blank. There is one IF statement per column of data. I will explain:
IF(B2<>"",B$1&" "&B2&"; ","")

Check to see if B2 has a value. The <>"" part translates to "not equal to NULL."
If B2 has a value, show [B1 B2; ].
If B2 is blank, show nothing.

The changes you mentioned that I was referring to were A) auto increment and B) adding columns. Those were not part of the original requirement and drastically change the way the solution needs to be structured.

Perhaps when you upload the sample file we can get you the right solution.

Cheers.