Solved: get Location count and get next Location

[CCkfm2000]

Help :help

I've got a spreadsheet with 4 columns of data

Column A Loc_A list of location for each s sku from column B
Column B Sku list of sku's
Column C Loc_B list of location
Column D Cap capacity in each location for column C


Cell G2 18050601 will change hour by hour

Cell G3 find the 1st record of the value from column B and get the location from column A.

Cell G4 =COUNTIF($A:$A,G3)

Cell G5 =VLOOKUP(G3,$C:$D,2,FALSE)

Cell G6 =IF(G5>G4,"get next location","ok")

Now if cell G6 is get next location

Cell G8 find the 2nd record of the value from column B and get the location from column A. But not the same location as cell G3

Cell G9 =COUNTIF($A:$A,G3)

Cell G10 =VLOOKUP(G3,$C:$D,2,FALSE)

Cell G11 =IF(G5>G4,"get next location","ok")

I've attached a example spreadsheet.

Many thanks and regards

:thumb

[Bob Phillips]

Help :help

I've got a spreadsheet with 4 columns of data

Column A Loc_A list of location for each s sku from column B
Column B Sku list of sku's
Column C Loc_B list of location
Column D Cap capacity in each location for column C


Cell G2 18050601 will change hour by hour

Cell G3 find the 1st record of the value from column B and get the location from column A.

Cell G4 =COUNTIF($A:$A,G3)

Cell G5 =VLOOKUP(G3,$C:$D,2,FALSE)

Cell G6 =IF(G5>G4,"get next location","ok")

Now if cell G6 is get next location

Cell G8 find the 2nd record of the value from column B and get the location from column A. But not the same location as cell G3

Cell G9 =COUNTIF($A:$A,G3)

Cell G10 =VLOOKUP(G3,$C:$D,2,FALSE)

Cell G11 =IF(G5>G4,"get next location","ok")

I've attached a example spreadsheet.

Many thanks and regards

:thumb

Not sure what you want.

[CCkfm2000]

ok will try and explain

What I need it to do is when I enter a sku in cell g2,
Looks up column B to the first match, get the location from column A in cell g3

Count the number of times the location it finds in cell g4

Vlookup cell g2 from column c:d in cell g5

Cell g6 does a check to see if the quantity in cell g4 and g5


if it?s the same
if different


if its different then it need to get the next location in column a for cell g8 and repeat it in cell g13 and cell g18.



I hope you understand it now.

[Bob Phillips]

ok will try and explain

What I need it to do is when I enter a sku in cell g2,
Looks up column B to the first match, get the location from column A in cell g3

Count the number of times the location it finds in cell g4

Vlookup cell g2 from column c:d in cell g5

Cell g6 does a check to see if the quantity in cell g4 and g5



if it?s the same
if different


if its different then it need to get the next location in column a for cell g8 and repeat it in cell g13 and cell g18.



I hope you understand it now.


What should the cap figure return? The formula that you have returns 32 every time.

[CCkfm2000]

What I need to do is to enter a sku in cell g2

I need it then to look up in column B for the 1st entry and get the location from column A

Then I need to count the number of time the location is in column A figure A

Now to check the capacity in column D figure B

Now I need to check if the count of figure A and figure B are the same

If it's same I need to get the next Location down the list and check figure A and figure B

Till I get a mismatch.

Here is and example

49890
t103
32
32
same

next location will be

k36
20
20
same

this will carry on till there is a mismatch

I hope you can understand this.

[Bob Phillips]

What I need to do is to enter a sku in cell g2

I need it then to look up in column B for the 1st entry and get the location from column A

Then I need to count the number of time the location is in column A figure A

Now to check the capacity in column D figure B

Now I need to check if the count of figure A and figure B are the same

If it's same I need to get the next Location down the list and check figure A and figure B

Till I get a mismatch.

Here is and example

49890
t103
32
32
same

next location will be

k36
20
20
same

this will carry on till there is a mismatch

I hope you can understand this.

Nope! I understand all that b ut you don't explain how you determine what figure goes in capacity which is compared to the overall count. The rest I have got.

[CCkfm2000]

I do a VLOOKUP(G3,$C:$D,2,FALSE)
this give me the capacity

[CCkfm2000]

ok this is what i've come up with.

see attached file.

this is only a small version of the file.

with all the formulas my spreadsheet has grown over 7mb.

anybody got any ideas how i can reduce the size of the formulas.

as you see on the spreadsheet this will workout only 1 sku information,

but need to extent the search option to get up to 10.

please help

thanks

[CCkfm2000]

still not getting anywhere with this so i've decided to try another way

how do i write a vba code to read some values from a sheet?

i have some values in cell a2 to a24

what i need to do is count how many value are in a2 to a24

then put then in an array

this will get me a start.

thanks again

please help

[CCkfm2000]

i've found another way to do this so i'm going to mark this tread solved