Hi,
This is a problem I'm trying to solve for a few days (the data itself is much bigger).
It all explained in the sample file - I'd just add that there is a point I have hard time to solve:
Here is the code I wrote but it "fails" in turning points like in A41 or A99.
Thank you!
Please Login or Register to view this content.
Please * if you like the answer
Try this:
Does this achieve your goal?Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
@Alan - thanks it looks good. But look at the errors in lines 96-100, where Columns B, C, D should be like A. Because there is a kind of hierarchy - A>B>C>D.
Ah! I thought that may have been a typo in your file. I will look at it later today and see if I can be of help. No time right now. One question, however, If column A says Small Market, does that mean that columns B:D will always say small market?
I appreciate this Alan! As to your question: not necessarily. You can think about columns A-D as categories in a decreasing rate of "importance". So if A96 = "Small market" and B96, C97, D97 are empty then yes line 96 of B-D will get the value of A96 ("Small market").
Another way to think about it is Country-City-Quarter-Neighborhood. So if in line 96 all we have is the country name (column A) then the rest of this specific line columns (B-D) will get the value of the country name.
Here is some revised code that I think should do the trick for you.
Shabat ShalomPlease Login or Register to view this content.
@Alan thanks, it's much better now.
But still there is a problem with the range D93:D95 (see attached file).
Actually this problem is general. The code gave the range D88:D100 the same value ("Packed_Cooled_Food") where actually the value in C93 ("Frozen_HotDog") should be "stronger" and so give the range D93:D100 its value ("Frozen_HotDog") rather than the value the code gave ("Packed_Cooled_Food").
It's kind of a complicated hierarchy.
Thanks again!
What in your worksheet would trigger this? What would the logic be to that could be coded. Tell me in simple language the logic behind that using the concept if A then B but if C then D because I don't see this.
Alan - all is explained, I hope in a good way, in the new sample file I attched.
Thank you.
This is beyond my abilities. I will refer to others to look at this.
Ok - thanks for your efforts.
Maybe:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
@TMS - Thank you, thank you!It definitely works with the sample file. Now I'll have to check it on the "real" data, and of course get back to report here.
Looked OK ...
Regards, TMS
it works perfectly with the small chank of real data I have at home. Only tommorrow I'd be able to check it on the whole database at work. Best forum in the world!
@TMS, please check PM.
Or:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks