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,

Yes, I would like to try and merge the two if that is possible?

Ok, see your point about the table set, this was wrong on my part, school boy error done in haste.

Thanks

 

[sandy666]

so use Merge Queries (as new)
select second column in the first table and second column in the second table (because first column doesn't have any common values, I checked it randomly)
then choose appropriate Join Kind and OK

 

[DavidG007]

so use Merge Queries (as new)
select second column in the first table and second column in the second table (because first column doesn't have any common values, I checked it randomly)
then choose appropriate Join Kind and OK

Hi Sandy, as you can obviously tell I am not the best with Power Queries........ just struggling to select Column's as per above so that i can make the 2 queries one.

Thank you

 

[sandy666]

column???

you mean here?

 

[DavidG007]

Hi Sandy,

Yes, but I can only get the results of the first query to show?

https://drive.google.com/open?id=1oyV0lfC97vBPt7Xd8oFggcUP5F-gDZTz

 

[sandy666]

which one is the first query and which one is the second query? (sheet name)

wrong question....

do you know the second query is from two lines only?
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]"3150","3153","3300","3301","3302","3303","3304","3308","3314","3315","3321","3327","3328","3338","3342","3345","3352","3354","3364","3365","3370","3372","3373","3395","3416","3419","3421","3422","3451","3452","3453","3454","3507","3508","3514","3521","3522","3528","3530","3535","3544","3551","3556","3565","3582","3598","3815","3851","3852","3854","3856","3864","4213","4220","4320","4606","4648","4691","4851"[/td][/tr]

[tr=bgcolor:#FFFFFF][td]"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]
[/table]

 

[DavidG007]

which one is the first query and which one is the second query? (sheet name)

wrong question....

do you know the second query is from two lines only?
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]"3150","3153","3300","3301","3302","3303","3304","3308","3314","3315","3321","3327","3328","3338","3342","3345","3352","3354","3364","3365","3370","3372","3373","3395","3416","3419","3421","3422","3451","3452","3453","3454","3507","3508","3514","3521","3522","3528","3530","3535","3544","3551","3556","3565","3582","3598","3815","3851","3852","3854","3856","3864","4213","4220","4320","4606","4648","4691","4851"[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]"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]
</tbody>[/TABLE]

Hi Sandy,

Yes, but those 2 lines result in several hundred lines of output as you can see from the query result, the merge query just doesn't like the join for some reason?

 

[sandy666]

test Inner join (only matching rows), in your last excel file you'll see there is nothing to match so something went wrong

 

[DavidG007]

test Inner join (only matching rows), in your last excel file you'll see there is nothing to match so something went wrong

not too sure, both queries look fine and give the correct answer, can't see why they wouldn't merge?

 

[sandy666]

could you post a link to the last file to be sure we are talking about the same thing?