• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merge and sequentially number data

S

subhi

New Member
The attached sheet shows 4 columns.

I need an Excel formula to achieve the following scenario:

If a value exists in column A (for example, the value in cell A2), place the same value in column D (D2) and continue this pattern for the subsequent rows.
If a value is empty in column A, concatenate the contents of cells B and C, Subsequently, add a continuous series of numbers for the latest 3 digits based on the preceding cell's value, which shares the same first 5 digits of colomn D. The outcome will be placed in column D.

The correc results are Yellow colored.
 

Attachments

  • Merge and sequentially number data.xlsx
    9.1 KB · Views: 2
See formula in cell E3, copied down. It may have to be array-entered (use Ctrl+Shift+Enter instead of plain Enter to commit the formula to the sheet depending on your version of Excel. Cell E2 is a bit different.
 

Attachments

  • Chandoo57617Merge and sequentially number data.xlsx
    13.3 KB · Views: 4
p45cal said:
See formula in cell E3, copied down. It may have to be array-entered (use Ctrl+Shift+Enter instead of plain Enter to commit the formula to the sheet depending on your version of Excel. Cell E2 is a bit different.
Click to expand...
Thanks alot.
Based on my scenaios, Your formula is working perfect.
But what if I have the below scenario, Could you please share the new formula to achieve it.

If there are some values exist in column A and there are empty cells between them.
If a value exists in column A (for example, the value in cell A2) place the same value in column D (D2) and continue this pattern for the subsequent rows.
If a value is empty in column A, concatenate the contents of cells B and C, Subsequently, add a continuous series of numbers for the latest 3 digits based on the preceding cell's value, which shares the same first 5 digits of colomn D. The outcome will be placed in column D.

The correc results are Yellow colored.
 

Attachments

  • Merge and sequentially number data.xlsx
    9.4 KB · Views: 3
In cell D7 of your latest workbook you have the yellow highlighted desired result of EBU36001, yet in cell A13 you already have EBU36001. I'm going to assume this is a typo and that A13 should be something else, perhaps beginning ERE36… . So if I overwrite A13 with anything else, just to take it out of the equation altogether, then:
In column A we have EBU36000 and EBU36002. Are you saying you want D7 to be the missing EBU36001 between EBU36000 and EBU36002?
If so it's going to be a very complicated formula indeed and I wouldn't like to write it. At the moment, my formula adds 1 to the highest existing number with the same Region/LinkCode prefix. A user defined function in VBA would be easier to put together if that's a possibility?

Important: what version of Excel are you using? An examination of your file suggests it could be Excel 2016.

Does your Excel have:
LET
VSTACK
LAMBDA
as worksheet functions available to you? Yes/No to each of the 3 functions above please.
 
p45cal said:
In cell D7 of your latest workbook you have the yellow highlighted desired result of EBU36001, yet in cell A13 you already have EBU36001. I'm going to assume this is a typo and that A13 should be something else, perhaps beginning ERE36… . So if I overwrite A13 with anything else, just to take it out of the equation altogether, then:
In column A we have EBU36000 and EBU36002. Are you saying you want D7 to be the missing EBU36001 between EBU36000 and EBU36002?
If so it's going to be a very complicated formula indeed and I wouldn't like to write it. At the moment, my formula adds 1 to the highest existing number with the same Region/LinkCode prefix. A user defined function in VBA would be easier to put together if that's a possibility?

Important: what version of Excel are you using? An examination of your file suggests it could be Excel 2016.

Does your Excel have:
LET
VSTACK
LAMBDA
as worksheet functions available to you? Yes/No to each of the 3 functions above please.
Click to expand...
 
Realy approciated for your support and cooperation.
You are right, the correct value in cell A13 is ERE36001 not EBU36001
also I want D7 to be the missing EBU36001 between EBU36000 and EBU36002.
I know this will be a complicated formula, but please informme if you get something.

My Excel has
LET : Yes
VSTACK : No
LAMBDA : No
 
Both the absence of VSTACK and you wanting to assign missing values makes it difficult for me to put together a formula for this.
I think I can put together a user defined function using VBA which would mean that the workbook would have to be a .xlsm file and macro-enabled but the formula you'd see on the sheet might be as simple as, for example in cell D6,:
=NewLinkCode($A$2:$A$19,D$2:D5,A6:C6)
If that's something you can use I'll do it (it won't be before the day after tomorrow). Awaiting your response.
 
I appreciate your feedback.

I have used the formula you shared earlier, and it is working perfectly at the moment.

Thank you once again for your tremendous effort.
 
You must log in or register to reply here.
Back
Top