Need Help: Formula to sort stock in clothing company's computer system

[rkobeyer]

Hi there,

I really hope someone can help me, this job normally takes a whole day of mundane data entry to complete. This needs to be done twice a month so if I can do this with excel you will save me 3 weeks of boring work.

A product represented by an 'S Code' in Column A (see below) needs to have values put into the columns F,G & H that apply to that product.

if A2 = "100001" and any cell in column D also = "100001"
then input in cell F2=1, G2=2 and G2=3 then F3=1, G3=2 and G3=3 etc.
until row 11 where it wont enter anything because that has a different product "100019"

This product "100019" has a value of 0 in Column B therefore nothing should be entered in columns F,G & H

The formula then continues matching Column A with Column D until all data has been entered.

NOTE: Each "S Code" is one product which can have up to 9 "P Codes" which are codes for the product's different sizes.

	A	B	C	D	E	F	G	H
1	S Code	Stock		S Code	P Code	On Hand	Min Stock	Max Stock
2	100001	17		100001	100001	1	2	3
3	100019	0		100001	100002	1	2	3
4	100028	30		100001	100003	1	2	3
5	100037	3		100001	100004	1	2	3
6	100055	11		100001	100005	1	2	3
7	100064	5		100001	100006	1	2	3
8	100073	25		100001	100007	1	2	3
9	100100	9		100001	100008	1	2	3
10	100109	15		100001	100009	1	2	3
11	100127	28		100019	100019
12	100136	23		100019	100020
13	100154	16		100019	100021
14	100163	70		100019	100022
15	100172	1		100019	100023
16	100181	5		100019	100024
17	100190	17		100019	100025
18	100208	11		100019	100026
19	100217	0		100019	100027
20	100226	28		100028	100028	1	2	3
21	100235	-2		100028	100029	1	2	3
22	100253	10		100028	100030	1	2	3
23	100280	1		100028	100031	1	2	3
24	100289	1		100028	100032	1	2	3
25	100298	15		100028	100033	1	2	3
26	100316	1		100028	100034	1	2	3
27	100325	7		100028	100035	1	2	3
28	100334	-4		100028	100036	1	2	3

<tbody>
</tbody>

I hope I've explained this well, any questions please let me know.

Looking forward to hearing from you,

Russell

 

[rkobeyer]

No one knows how to do this?

 

[WallyBackman]

This formula in Cell f2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",1)
This formula in Cell g2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",2)
This formula in Cell f2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",1)
This formula in Cell h2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",3)
Copy the 3 formulas down for each row of data.

 

[rkobeyer]

This formula in Cell f2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",1)
This formula in Cell g2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",2)
This formula in Cell f2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",1)
This formula in Cell h2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)=0,"",3)
Copy the 3 formulas down for each row of data.

Thanks so much! that works which is pretty exciting.

However it would be nice if the formula excluded anything from column B that is below 0 and if it cant match the code from column D with column A could the formula input 0 instead of #N/A ?

 

[WallyBackman]

This formula in Cell f2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)<=0,"",IF(ISERROR(VLOOKUP(D2,$A$2:$B$28,2,0)),0,1))
This formula in Cell g2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)<=0,"",IF(ISERROR(VLOOKUP(D2,$A$2:$B$28,2,0)),0,2))
This formula in Cell h2 =IF(VLOOKUP(D2,$A$2:$B$28,2,0)<=0,"",IF(ISERROR(VLOOKUP(D2,$A$2:$B$28,2,0)),0,3))
Copy the 3 formulas down for each row of data.