Challenge :) formula or VBA

[DavidG007]

I have an issue that I don't know how to easily resolve. I need to convert the following line of text.

Column 'C1' would typically be this (but will be multiple lengths);
Case Is = "400000","400100","400200","400300","400400","400500","400700","400900"

Column 'D2' would say 'Revenue'

What I need to be able to do is break down Column 'C1' and include the text in Column 'D2'

My final answer would look like this;

Column A Column B
400000 Revenue
400100 Revenue
400200 Revenue


etc etc.

The formula or code would then need to do exactly the same check on subsequent rows as the pattern

I really hope this makes sense and there are a few of you that fancy the challenge

massive thanks for any replies

 

[DavidG007]

..... the 2nd link worked fine, a massive thank you and for your patience!

 

[sandy666]

you are welcome

now you can use Thanks/Like buttons

 

[sandy666]

Thanks but enough in the post with solution

 

[DavidG007]

Hi Sandy,

I am really hoping you can help one final time, the query you helped developed works fine and really please with, but, I need to amend it so that it performs another check;

To help explain I have attached a link to the file but basically the extra step needs to follow;
https://drive.google.com/open?id=1ZuJX-vblubBPBJYLRnET4spv2L13NO_J


Q - the first field, 3150, needs to be fixed and then joined with every item individually on the subsequent rows.

[TABLE="width: 365"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Case Is = "3150","3153","3300","3301","3302","3303","3304","3308","3314","3315","3321"[/TD]
[/TR]
[TR]
[TD]Select Case fields(5) 'Do the BA Lookup[/TD]
[/TR]
[TR]
[TD]Case Is = "2E00","2.00E+00","3100","3200","6800","7000","8200","8500","4I00","5C00","5N00","5Q00","9P00","A300","A600","AH00","B300","BB00","BY00","D300","D700","F900","FI00","FJ00","FK00"[/TD]
[/TR]
[TR]
[TD]Return "COS_CFR"[/TD]
[/TR]
[TR]
[TD]Case Is = "1800","4900","5100","5900","7500","8700","4Y00","5F00","5O00","5W00","6F00"[/TD]
[/TR]
[TR]
[TD]Return "COS_LFR"[/TD]
[/TR]
</tbody>[/TABLE]

so that the result would be;
[TABLE="width: 257"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD]3150 + 2E00[/TD]
[TD]COS_CFR[/TD]
[/TR]
[TR]
[TD]3150 + 2.00E+00[/TD]
[TD]COS_CFR[/TD]
[/TR]
[TR]
[TD]3150 + 3100[/TD]
[TD]COS_CFR[/TD]
[/TR]
[TR]
[TD]3150 + 3200[/TD]
[TD]COS_CFR[/TD]
[/TR]
[TR]
[TD]3150 + 6800[/TD]
[TD]COS_CFR[/TD]
[/TR]
[TR]
[TD]3150 + 6800[/TD]
[TD]COS_CFR[/TD]
[/TR]
[TR]
[TD]3150 + 7000[/TD]
[TD]COS_CFR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[/TR]
[TR]
[TD]3150 + 1800[/TD]
[TD]COS_LFR[/TD]
[/TR]
[TR]
[TD]3150 + 4900[/TD]
[TD]COS_LFR[/TD]
[/TR]
[TR]
[TD]3150 + 5100[/TD]
[TD]COS_LFR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc etc[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

On the attached file, the 1:1 mapping can all be in their own column, when it comes to the extra check as per above, again they can be in their own column too.


Big Question - Is this possible with the existing query?

Huge thank you

 

[sandy666]

data from Test v2 (blue) is in a single column?

 

[sandy666]

maybe post link to the xml file and describe what you want to achieve

 

[DavidG007]

Hi Sandy, Yes, I altered the way the code exports to make it easier to separate out the simple 1:1 (as before) with the ones that have to do the additional step.

 

[sandy666]

before "Case" was in the first column, "Return" in the second column
your changes do not make it easy, but even hinder it
as I said: post a link to shared xml file and describe what you want to achieve (for whole example, no partial)

 

[DavidG007]

Hi Sandy

Ok, let me save down and I will post shortly

Thank you

 

[DavidG007]

Hi Sandy,

This is the link to the text code which I then save into excel to manipulate;
https://drive.google.com/open?id=1JGD1zOTnBpwNLeYfOH0MbQn-OpMx9KTR

The excel file is found here;
https://drive.google.com/open?id=1E8d5aKOF-pAvU6iDG1iiCjRLh4qFLVj2

On the 'Test v3" worksheet;

Cell G53 needs to be fixed and then to join with every item in " " on lines 53, 57, 59, 61 etc and return what is in column 'H'

e.g. G53 first item is "3150" this would concatenate with "2E00" and return "COS_CFR"

Column A Column B
3150 = 3150 + 2E00 COS_CFR

then again,
3150 + 2.00E+00 COS_CFR

etc

"3150" will potentially have 25 lines by merging with whats on line 55, then another 29 lines with merging on line 57 etc. we then repeat the process for the next item in cell G53, namely "3153"

Ultimately creating a 1:1 rule for the line of code

Hope this helps