Link to home
Create AccountLog in
Avatar of Curtis Long
Curtis LongFlag for United States of America

asked on

Help with spreadsheet

I am working on a spreadsheet that needs to show unique lot numbers from a reference sheet, but it needs to be tied to a date. For example, Lot 19-104 has data from August and September, so I need to have lot 19-104 returned twice, once for August and once for September.  The reference sheet (Import Data) has a column for a date and a column for the lot number.  The Production sheet has a column for the lot number and a column for the month. I can send a copy of the workbook so you can see what I'm working with (I can't see how to attach it here).  Currently, I have just typed in the lot numbers and the date (month) comes in using xlookup using the lot number as a reference.  I tried to bring the lot numbers in using UNIQUE, but I just get a spill error, even when I clear all the contents out of the column.  The Import Data sheet is linked to an external data source.
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

This is where you attach a file:

User generated image
Avatar of Curtis Long


2020 Production.xlsx

Thank you.  I was looking for a file folder icon.
You can use a pivot table to get a unique list, but I am sure there is a lot more to this..  See UniqueList in attached.  Are you trying to transfer some of the Input tab to the Production tab?

I do want to pull from the Import tab to the Production tab.  The Production tab is the report that the Plant Management uses to monitor production, and the Plant Controller uses it to accrue potato costs by month.  The accrual is why the lots need to be divided by month.  So for lot 19-104, I need to have a separate line for each month.  For example:
Month   Crop Year   Variety      Field   Cellar   Bay      Lot #    Grower   Dates Run         Run Nos
August   2019         Norkotah                                    19-104    JKF      23,26,28,29,30   118,120,126,128,132
Sept      2019          Norkotah                                    19-104   JKF       3,4,6                   133,134,138  

Does that make sense?

Do all the fields on the Production tab come from the Import tab?
Most columns have formulas that refer to the Import tab.  Currently the Lot # and Crop Year do not.  Some of the others are calculated from other columns that refer to the Import tab.  The Import tab comes from another workbook that I used Power Query to pull from.
Avatar of Flyster
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That works perfectly!  I don't completely understand the formulas you used, but I was able to make some small tweaks where it was needed.  Thank you so much!
You're welcome. Happy to help out!