PDA

View Full Version : Find group of records that match index and then do xxx



YellowLabPro
10-08-2008, 05:55 AM
I could use some help here: It has been a while since I have critically thought and wrote code, so some hand holding would be appreciated greatly.

My goal is to evaluate if an item that is in-stock physically, which is recorded in my store point of sale, and if it is missing its image on my website online store.

I have a master inventory report that contains all the items and their information, this is generated from the store's point of sale software. I export this report out to Excel.

I generate a report that tells me if a product image is missing from my website for an item. This report is in an Excel worksheet natively.

My inventory records are either A) Parent item w/ children, B) Children, C) Parents w/out children.
For example:
"~P GL1ENTSBK" is a shoe and has the following records:
"~C GL1ENTSBK10", "~C GL1ENTSBK11", "~C GL1ENTSBK12".

"~P GL1ENTSBK" is the parent and has a qty of 1998, which is for reference purposes only.

"~C GL1ENTSBK10", "~C GL1ENTSBK11", "~C GL1ENTSBK12" are the children items and would contain inventory qtys. of 2, 1, 0, hypothetically.

A Parent record w/out children would be an item such as a watch, it has no items w/ sizes, and is reflected as "~P NI1SURBK", and would have a real count of lets say 2.

Note: When presented to the public on the website the "~P" and "~C" are removed from the title.

Here is my problem:
The report that is generated from the website for items missing their product photos may or may not have a qty greater than zero b/c it looks at the parent's count which is 1998, not at the children's count.

In reality this item may be out of stock, and or discontinued and I don't wish to physically search for this item to photograph. The dilemna is I have approx. 7000 items to look for in my list, but w/ all the sizes in the store in all the different categories it is conservative to say, a pain in the arse... and super time consuming, which translates to expensive. And we are down to 4 people working in my shop.

So I need a way to look at the Parent records and determine if their children records have a quantity of greater than zero. By achieving this I can locate the item by its size and photograph it. I can also evaluate that if it is only one size left to ignore it.

Here is what I have figured out, but not sure how to use it:

If the first 2 characters are "~C", If the first 8 characters match in the group similar records then record or add the totals for these records and record. If it is zero then don't record.

A formula I have used to do something similar is:
=IF(C2="","",IF(LEFT(C3,2)="~P","",IF(AND(J2="",LEFT(C2,2)<>"~C"),IF(LEFT(B2,8)=LEFT(B3,8),B2,""),IF(LEFT(B2,8)=LEFT(B3,8),J2,""))))

I am going to upload a worksheet that will be separated by point of sale and download of missing images.

I know this is long, but thought it better to lay the foundation rather than go back and forth trying to explain.

Thanks for taking the time to read through all this, and hopefully makes some sort of sense.

Doug

mikerickson
10-08-2008, 08:01 AM
This formula in R21 and dragged down will return the inventory on hand of the child items of a parent item. It will return 0 for those rows with Child items or childless parent items.
=IF(AND(LEFT(D21,2)="~P",1<COUNTIF(D : D,"*"&MID(D21,4,1000)&"*")),SUMIF(D : D,"*C "&MID(D21,4,1000)&"*",Q:Q),)

(Ignore the spaces in the column reference D : D )