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.
Microsoft ExcelMicrosoft Office
Last Comment
Flyster
This is where you attach a file:
ASKER
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?
EE1.xlsx
EE1.xlsx
ASKER
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?
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!