Storage refill problem

Copper Contributor

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

Item nrNameLocation
123Cup1A
123Cup1B
123Cup1C
1234Plate2A
1234Plate2B
1234Plate2C

 

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.

 

Also!

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?

Maybe if you link from another document?

3 Replies

@Fredirk 

Please check attached file 

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 

 

 

@Kodipady 

Thank you for the fast reply. And thank you for helping me.

Sorry about my explanation, there was alot of google translate involved..

 

But this worked much better than the things I had in mind and the guys at work loved it. 

Thank you so much!

@Fredirk 

Thanks for the feedback and I am glad it worked for you !!