Duplicate data need to be extracted in proper order to look pricing history

[rosn.dhakal]

Hi there,

I'm trying to come up with a data modeling where I can pull up the last purchase price history with vendors, unit, and date associated with that purchase. This is what my warehouse report have for me.

COLUMNS: (SKU, SKU Name, Unit, Last Price, Date of Purchase, Vendor Name)

Since it is the record of all the purchase made from 2016, I'm having a hard time creating that model. Duplicate SKUs in the SKU section. I can use =index to pull up the latest pricing but I can't find a way to see those date, pricing, vendor together. Maybe pivoting is the answer which I'm still learning.

I will really appreciate if someone can guide the right way. Thank you and hope you have an amazing New Year ahead of you!

[FlameRetired]

Need some more clarity on the issues.

A small Excel file representative of what you are working with and what the goal(s) specifically look like is usually helpful. Think BEFORE and AFTER.

If you are unfamiliar with how to do this:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[rosn.dhakal]

I just uploaded the before version. I was trying to come up with the after version but I guess that's where my confusion lies. I'm trying to pull the pricing history based on its date at my power bi. Since the unique identifier
(SKU) is not unique, I'm having a hard time and assumed maybe pivoting has a solution. Thanks a lot for taking your time to assist me!

[FlameRetired]

I couldn't come up with a Pivot solution that reflects the layout I think you are wanting.

Added some extra fake data in order to test these. It took 4 formulas.

SKU:

Please Login or Register  to view this content.

PRICE:

Please Login or Register  to view this content.

Vendor which might be simpler but didn't find a way yet:

Please Login or Register  to view this content.

DATE:

Please Login or Register  to view this content.

They reference one another's output, and were entered in this order SKU, VENDOR, DATE, PRICE.

[rosn.dhakal]

No problem at all. I can't thank you enough for your time. I do wonder if there is a way out of this though. Thanks again!

[FlameRetired]

You're welcome. Glad to help. Thanks for the feedback and added rep.

One last thing.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FlameRetired]

An afterthought.

This is simpler if there is room in the plan for a helper column this in G4 (helper) and filled down.

Please Login or Register  to view this content.

Then with a single formula in J4 filled down and across until you get blanks.

Please Login or Register  to view this content.