conditional concatenation

[sheri100]

Hello,

I'm really new to VBA and need some help with the following:

I have a worksheet that starts with Original Value, Start, End, and I need to generate the New Value.

Hello,

I'm really new to VBA and need some help with the following:

I have a worksheet that starts with Original Value, Start, End, and I need to generate the New Value.

I have attached an example of the data.

New Value explanation:
A & B are concatenated because they have the same start date and they have not reached their end date.
A, B, C are concatenated because C's Start <> B's Start and they have not reached an End.
A is dropped because D's start is >= to A's End.
There could be hundreds of rows of data to concatenate depending on their start and end dates.

I posted this question here as well: http://www.mrexcel.com/forum/showthread.php?t=490757

Thanks in advance for any help you can give me, I'm getting desperate.

[rylo]

Hi

1) How is an End determined? As it is 2 years in the past, the current date doesn't seem relevant.

2) How is the data processed? Sequentially down the page, or all data for each item in the range?

rylo

[sheri100]

1. Start and End are program dates that are determined outside of what I'm trying to do. The dates will always be in order from oldest to most recent.
2. If I understand what you're asking it is sequential down the page. From the first row, it looks to see if the first start date = second start date, if it <> then the first row would be "A", if it is = then the first row would be "A, B". Everytime there is a new start date I need to start a "New Value" row. Values continue to be concantenated to the string as long as their end date > the next values start date.

Thanks again for your help.

[rylo]

Hi

Can you update your example file with another 10 or so example lines. I'm not sure I really follow exactly what is to happen when, and expanding your example file may help to clarify.

rylo

[sheri100]

See sheet 2 in the attached file, hopefully it will help.

Thanks

[rylo]

Hi

Should the condition in F3 start B4<> B3???

Also, why aren't i and j added in D7 rather than waiting until D8?

rylo

[sheri100]

Yes it should say B3<>B4.

i, j aren't added in D7 because there are end dates (b, c, d) before their start date. if they don't have an end date they never get removed.

Sorry for the delay in respnding.

Thanks

[sheri100]

I still haven't figured this out if anyone is willing to help.

Thanks

[rylo]

Hi

Still having trouble actually understanding how this is processed. I can't determine the pattern, or hierarchy of rules that you are using to accumulate the strings.

Also, going back to your post 5, you have the statement

Everytime there is a new start date I need to start a "New Value" row.

. However, the output in your example file doesn't really seem to follow this. To me, you shouldn't need to change the value in D3 as there is no new start date. B2 and B3 are the same. Your next "new value" shouldn't appear until D4, then D6, D7...

Maybe if you step out exactly your process. Say you are in D2. Exactly what do you do when you are doing this manually? Look at C2. > B2. Cumulative String is "a". Look at B3. Same as B2. Look at C3. > B3. Cumulative String "a,b". Look at B4. Ahh, different from B2, so paste cumulative string in D2. Move to D3. Then what do you do???

rylo

[sheri100]

Hi

Still having trouble actually understanding how this is processed. I can't determine the pattern, or hierarchy of rules that you are using to accumulate the strings.

Also, going back to your post 5, you have the statement . However, the output in your example file doesn't really seem to follow this. To me, you shouldn't need to change the value in D3 as there is no new start date. B2 and B3 are the same. Your next "new value" shouldn't appear until D4, then D6, D7...

Maybe if you step out exactly your process. Say you are in D2. Exactly what do you do when you are doing this manually? Look at C2. > B2. Cumulative String is "a". Look at B3. Same as B2. Look at C3. > B3. Cumulative String "a,b". Look at B4. Ahh, different from B2, so paste cumulative string in D2. Move to D3. Then what do you do???

rylo

continuing from what you wite:

look at B4, it's different from B3, look at B5, = B4, add c,d, look at C2:C3 > B4, look at C2:C4>B5 , D3=a,b,c,d (we skip to B6 because B5 has been added)

Look at B6, <> B5 or B7, e is added, look at C2:C5, >=B6, look at C6, ="", D4= a,b,c,d,e

Look at B7, B7<>B6 or B8, Look at C2:C6, > B7, NO, B7 = C2 so a is removed, D5 = b,c,d,e,f

Look at B8, <>B7, =B9, add g,h, look at C3:C8, > B7:B8 so nothing is removed, D6 = b,c,d,e,f,g,h

Skip B9 because it is used above

Look at B10, > C3:C5, remove b,c,d, D7 = e,f,g,h

Look at B10, <> B9, = B11, add i,j, look at C7:C9, "" so nothing is removed, D8 = e,f,g,h,i,j

Look at B12, > C11, remove i, D9 = e,f,g,h,j

Look at B12, <> B11, = B13, add k,l, look at C7:C10 (C11 already removed), " " so nothing is removed, D10 = e,f,g,h,j,k,l

As for post 5 you mention above, each unique date starts a new value: we start a new value in D3 because we have already used the dates in B2 and B3 and we are now looking at B4.

I hope this helps, once I get to work I will post a file with some code I have been working on, it may help explain things better.

Thanks

[sheri100]

Here is what I have worked on (see Module 1) for the code. On sheet "Data" I have what the new values and dates should look like and note what I don't have working. I suspect that the way I have written the code won't allow me to get this last condition working and that is where I'm stuck.

Thanks

[rylo]

Hi

Getting clearer. Didn't realise that you were doing things on each unique date!

Looking at the output on sheet data there are 2 items you are still having issues: 5/8/2008 and 1/10/2008. Can you advise exactly what the output for those 2 dates SHOULD be. I'm not sure from your output if that is the correct result (including the extra commas), or if it should be something else.

rylo

[sheri100]

Hi

Getting clearer. Didn't realise that you were doing things on each unique date!

Looking at the output on sheet data there are 2 items you are still having issues: 5/8/2008 and 1/10/2008. Can you advise exactly what the output for those 2 dates SHOULD be. I'm not sure from your output if that is the correct result (including the extra commas), or if it should be something else.

rylo

Extra commas are typo-copy/paste error.

The output ("A31:B39") on "Data" is correct (except for extra commas), when I run my macro I can't get 8/5/2008 or 10/1/2008 values to generate correctly (see "Policies") column "B" e,f,g,h should be inserted on "Policies" after "B5" and e,f,g,h,j should be inserted on "Policies" after "B7".

The logic behind these 2 is: the start date currently being evaluated in the loop > their end dates.

I somehow need to test for this logic in my code along with the other conditions I'm already checking...I'm just not sure how to get them all to work together correctly.

Thanks Again

[rylo]

Hi

Using your example file, create a new sheet called Output.

Then try running

Please Login or Register  to view this content.

If that is giving you the correct output, then you can replace the new sheet (Output) with Policies.

See how that goes.

rylo

[sheri100]

This looks great, I will run it with more data when I get to work tomorrow.

I really appreciate all your help!

[sheri100]

I tried this out with more data and it worked great.

Thanks