Adding new record IDs from partly existing IDs

Hi Community,

I need to get Target_order_of_sale(output) from Current_Order_of_sale. As you can see if there is value in "current.." column then that value should go in output, and if not it should get value depending on "current" and "class" priority(by that I mean that A is highest and b2 lowest priority).

I was trying multiple ways to solve it , but cannot find a correct solution.

Help!!!!

Best

REORDER_OF_SALE.xlsx

Preparation

Accepted answers

TonyA

The only way I could figure out how to do this was with a pretty complex multrow formula. Another option would be to use a crosstab and join to add the current values for each product for all classes to each line and then use an equally complex standard formula.

My solution is attached.

It is important that all rows for each product are grouped and the classes are sorted in ascending order.

Order of Sale.yxmd

All comments

TonyA

I was going to ask how you are assigning priorities, but I think I see it.

ShadeDaddy

i need to always look for each product(3records each product). First is to put value from "current_order_of_sale". And if it is null adding value by "class", value needs to be different then in "current_order_of_sale" for the same product. So that I get distinct RecordIDs on each Product. My head is going to explode. 🙂

TonyA

My solution is attached.

It is important that all rows for each product are grouped and the classes are sorted in ascending order.

Order of Sale.yxmd

ShadeDaddy

@TonyA you are the King!!!!

It works!

That is fantastic!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335