View Full Version : Solved: get Location count and get next Location
CCkfm2000
01-12-2006, 08:50 PM
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
01-13-2006, 04:51 AM
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
01-13-2006, 07:06 AM
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
01-13-2006, 07:37 AM
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
01-13-2006, 05:05 PM
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
01-13-2006, 05:23 PM
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
01-13-2006, 05:34 PM
I do a VLOOKUP(G3,$C:$D,2,FALSE)
this give me the capacity
CCkfm2000
01-16-2006, 10:34 PM
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
01-19-2006, 02:55 AM
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
01-20-2006, 01:28 AM
i've found another way to do this so i'm going to mark this tread solved
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.