CONCATENATE with Auto Numbering and Eliminate the Empty Value cell(Cells contains Formula)

Dear All,


I am not much aware of the formulas in excel so, could you please any one help me to solve my issue.

Cell D to F Contains some text which is defined by using IF condition in Excel. I need to Concatenate this cells in Cell G but few of the cells May not have any values. So concatenate need to be done only for value available cells and eliminate the non-value cells (We can't say it is blank cells since it is having formula) And also each value prefix contains the numbers (Auto Numbering for Each value in Cell G) in Cell G with comma between one cell value to another cell value which will help to segregate the concatenate cell values. Incase if any of the Cells Contains #N/A then eliminate that cell and concatenate the remaining cells.

For Your Better understanding, I have attached the sample excel sheet with available data with my requirement.

I would appreciate if any one respond quickly.

Regards,
KK

Hello,

See if the file attached helps you out. I have added 3 helper columns and hide it once you understand.

I would apply if error formula for Weight note, Material note, unit note columns to rule out the error.

Check if this works for full data

First of all u should replace #N/A with using replace and remove tools(ctrl+H) and then
paste this formula in desired cell and drag down...

=TRIM(IF(COUNTBLANK(D2),"",COUNTA($D2:D2)&")"&D2)&" "&IF(COUNTBLANK(E2),"",(COUNTA($D2:E2)-COUNTBLANK(D2))&")"&E2)&" "&IF(COUNTBLANK(F2),"",(COUNTA($D2:F2)-COUNTBLANK($D2:$E2)&")"&F2)))

Hello Elayaz,

Your Formula working fine but it is really very tough for me to understand(I am very new to Excel formulas). Still I need to explore my requirement but Before exploring further Let me understand the formulas first.

I am new to excel formulas so, it will take some time to understand but I hope, I will quickly Learn the formulas.
Thanks for your Prompt reply and help.
Regards,
KK

Dear All,

Any one please help me to solve my problem as requested in my last post.

Thanks in advance

Regards,
Kk

=IF(OR(N(A1),ISTEXT(A1))=TRUE,COUNTA(A1)&")"&A1&","&IF(D1="","",COUNTA(D1)+1&")"&D1&",")&IF(E1="","",COUNTA(D1:E1)+1&")"&E1&",")&IF(F1="","",COUNTA(D1:F1)+1&")"&F1&",")&IF(G1="","",COUNTA(D1:G1)+1&")"&G1&",")&IF(H1="","",COUNTA(D1:H1)+1&")"&H1),IF(D1="","",COUNTA(D1)&")"&D1&",")&IF(E1="","",COUNTA(D1:E1)&")"&E1&",")&IF(F1="","",COUNTA(D1:F1)&")"&F1&",")&IF(G1="","",COUNTA(D1:G1)&")"&G1&",")&IF(H1="","",COUNTA(D1:H1)&")"&H1))

hope this for you...

Hi ,

This formula works fine in the normal excel sheet but this is not working when the cells are filled with formula (Blank data arrived with help of If formula).Hence the formula is activated for blank cells so the auto numbering is done for blank cells also

Could you please look this issue and help me again.

Regards,
KK

=IF(OR(ISNUMBER(A1),ISTEXT(A1))=TRUE,COUNTA(A1)&")"&A1&","&IF(D1="","",COUNTA(D1)+1&")"&D1&",")&IF(E1="","",COUNTA(D1:E1)+1&")"&E1&",")&IF(F1="","",COUNTA(D1:F1)+1&")"&F1&",")&IF(G1="","",COUNTA(D1:G1)+1&")"&G1&",")&IF(H1="","",COUNTA(D1:H1)+1&")"&H1),IF(D1="","",COUNTA(D1)&")"&D1&",")&IF(E1="","",COUNTA(D1:E1)&")"&E1&",")&IF(F1="","",COUNTA(D1:F1)&")"&F1&",")&IF(G1="","",COUNTA(D1:G1)&")"&G1&",")&IF(H1="","",COUNTA(D1:H1)&")"&H1))

This formula works on all types of cells in addition to the blank cell.......

Hello,

Thanks for your formula. But when I am trying to apply the formula in my sheet, it doesn’t works.
I have attached my original sheet where the formula applied in the cell q.
The cell q should concatenate the cell I & cell K to Cell P with auto numbering.
I have a problem in the auto numbring in the formula(Look the cell q result). Could you please look the issue in the attached excel and revert back to me with the correction.

Once again thanks for your help

Regards,
KK

Originally Posted by KSQUARE2K6

Hello,

Thanks for your formula. But when I am trying to apply the formula in my sheet, it doesn’t works.
I have attached my original sheet where the formula applied in the cell q.
The cell q should concatenate the cell I & cell K to Cell P with auto numbering.
I have a problem in the auto numbring in the formula(Look the cell q result). Could you please look the issue in the attached excel and revert back to me with the correction.

Once again thanks for your help

Regards,
KK

watch this workbook for required answer........

Thanks Ashishraj...

My problem got resolved...

Thanks,
KK

Hello Every one,

My problem got resolved by Ashishraj...

Thanks for every one support

Regards,
KK