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]

Hi Sandy,

Apologies, basically as per post #40 .

I need to have a single excel worksheet that has in;

Column A - Column1 from both NS_UD_Step1 + NS_UD2_Step2
Column B - Column2 from both NS_UD_Step1 + NS_UD2_Step2


please note the new file and link - https://drive.google.com/open?id=1zF...DVd_Dr7lQyR8-1

Thanks

 

[sandy666]

like this?

Excel file

I did it on duplicates, all the rest is like it was

 

[DavidG007]

Hi Sandy,

Please see link

Rule Split for Excel 1to1.xlsm

Thanks once again

Hi Sandy,

Apologies for being really cheeky but a while ago you gave me some very excellent help and I was just wondering if you would have any spare time to take a quick look at a post that I have made today in terms of a 'simple' powerquery?

Many thanks, David

 

[sandy666]

it was almost year ago so I need to re-read whole thread
I'll look at this later

 

[sandy666]

I fixed your code so try to continue :
see file