Tuesday, November 29, 2011

Dumping you data to Excel ... part #3

Had written this in 2010 .... click here ....

Here is part # 2  .... click here .....

Since then we have a couple of things to add ...here is part#3

-we try to limit the total number of sku's being pulled to preview at less than 150,000 ...yep - Exploris can have a LOT of items ... for our stores we pull store #1 all CQ lines then sure#1 X lines .... and so on thru the stores ... go do other things file you await the preview to come up ... takes a while - 3-4 minutes is a guess.  If you try TOO many -- something pukes in your  computer or EXPLORIS - not quite sure.
-we have a fast way to get all the text to numbers ( I think newer versions of Excel might have a better way ? ) by type a number1 in a cell and paste special "multiply only' to the columns needing the change
-BEFORE we do the above paste special--we sort out the  line totals --- we get all data into ONE main sheet ( our limit on excel is 1 million rows  - we have approx 350,000 rows for 3 stores ) and sort for BLANK PN Codes ...  these are parts that someone has manually entered ... these are the most problematic ... and we mark them with the word BLANK and send copies to store managers for review.
-remember if you are multi-store - add a column and mark your stores ( for us it is as simple as a 1 ..2   or 3 and we colour to flag easier .... see image
-we have not found a report much better than Inventory Info ...  not great by any means - but better than nothing -- line - part - desc - pn code - qty on hand - order point -  cost ( but no core cost ) - date added - date last sold - qty sold 12 mths - qty sold 24 mths ... have lots of other fields we can use -- but --we wait

SO --once you have all the data
-send replaced by data (oh - forgot to mention - an unmarked column you get the  superseding part #  --so sort for this data and for what stock you have on hand...  ( the image uses is this report - well - part of it )
-parts with blank PN codes

Then --as you work on a line .. you can grab just that data  ... for example in hard parts we look at our stock depth and see impact if we expanded stock depth ( yep - we do all our move ups ).  Or on things like RETAIL: lines - handy to see what one store sells that a 2nd store doesn't --and why/why not for expanding stock depth here

We change the column order --from the original data ...  have fun and work your data ....

No comments: