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]

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

Sure, & thanks
https://drive.google.com/open?id=1oyV0lfC97vBPt7Xd8oFggcUP5F-gDZTz

 

[sandy666]

I tested it for Inner join (only matching rows) and see nothing so it means there are no common records
I wonder where you see proper result? or if "nothing" is a proper result that's ok

 

[DavidG007]

I tested it for Inner join (only matching rows) and see nothing so it means there are no common records
I wonder where you see proper result? or if "nothing" is a proper result that's ok

I think the answer is that there are no common records on the join, so returns nothing, but this means that I can not merge the two queries?

 

[sandy666]

of course you can, it's depend what you want to achieve, no information is information too
read my first bottom line

edit:
if there is no match, merge return left or right table (depend of kind of join)

 

[DavidG007]

hum..... tried all the joins and it just wont merge?

 

[sandy666]

these two tables ARE merged, but there are no common records , but they are still merged.
you need to change the way of thinking and forget about formula style or vba style. Think like PowerQuery user

If there is no matches, you can:
- get info there is no matches (each information is information - good or bad but information)
- delete merge and see if there is something useful except merge
- PQ merge is like vlookup or index/match, where #N/A is null in PQ

but first you should define what you want (plan your work)

Power Query Merge

 

[DavidG007]

these two tables ARE merged, but there are no common records , but they are still merged.
you need to change the way of thinking and forget about formula style or vba style. Think like PowerQuery user

If there is no matches, you can:
- get info there is no matches (each information is information - good or bad but information)
- delete merge and see if there is something useful except merge
- PQ merge is like vlookup or index/match, where #N/A is null in PQ

but first you should define what you want (plan your work)

Power Query Merge

Thanks for the attached, still struggling though, its late here so calling it a day, could you help with the join? as per previous link?

Thanks for your continued support

 

[sandy666]

sure but you need to decide what you want to achieve

I repeat: if there is no any matches in these columns you can try merge by the first columns then if there is no any matches so it means merge doesn't make any sense, just for training only.
As I can see tables NS_UD2_Step1 and NS_UD2_Step2 doesn't contain any common values in each column.
I think you should UnMerge NS_UD2_Step2 then try to merge NS_UD2_Step1 and NS_UD2_Step2 again as you can see on the picture:

after that you will see any common values so it does make sense.
I don't know your way so hard to say this is a correct result or not, but we got a little step forward

here is a part of the result from above

Plan your work!

Edit:
and don't quote my post(s) because I don't see anyone else here so quoting doesn't make sense
quote only part of post if necessary

 

[DavidG007]

Hi Sandy,

I have to try and get this finished today as it is urgently needed, I tried the Merge Query as per you suggestion but I am still struggling, I have since tried the Append Query which give me what I need but unfortunately in a different column!

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

As you can see from the file link above, I have tried to create a link between the two queries so that the join may work but I am still missing the point I think!

Thank you

 

[sandy666]

Sorry but I can't help if I don't know what you want to achieve. I said it a few time but see no reaction. If you decide to tell me that with details we can talk more but for now I can post excel file only.
That's all.