Hello guys! I work at a warehouse and we do alot of work in excel. But today I ran into a little problem.
My boss wanted me to create an excelsheet so we can easily refill spots that's almost empty from our backup locations.
The idea is that we copy the item nr, name and lacation of alot of stuff from our system to excel.
But with all the duplicate items that we have on different locations it gets kinda messy..
An example of what it looks like after I copied some stuff into excel
I want to remove the duplicates (else the document will be over 600 rows, and no one wants to print that) but still want excel to remember the locations, because when 1A is almost empty I want 1B to pop up next to it if there is any amount left on that spot.
Is there a way to give the item nr a name and a location? Like, if I write 123 in A1, the name of the item will show in B1, and the location C3?
1. next to the actual data table you shared , i have created a pivot table that lists a summary by item number. if you need to have location clik on that small +(plus) sign next to the name
you can modify the pivot table as you need, but it is a easy to use tool. this should address your printing issue from 600 rows.
2. The name field in the data is a look up from a table in tab "Item Master" you can add more items to this. in sheet 1 you enter item Nr, formula fetches name from this table. you seem to have more than one location for each item, hence you may not be able to fetch Location based on item number.
Hope this help. let me know how it goes.
If you need any further help , i will be glad to help, but please upload a sample excel file with some realistic sample data