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.

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


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.