I have two columns (A) and (G) that have like product codes however

[genesisoxygen]

I have two columns (A) and (G) that have like product codes however
they are not in any particular order. In columns (H) and ( i ) are bin
numbers that are associated with the product codes in column (G) I
would like to put these bin numbers in columns (E) and (F)
Example of what my worksheet looks like:

Prod Code Item Name Location Qty Prod Code Bin 1 Bin 2
BD0036 Oxygen 10 2 BD0100 25
42
BD0100 Vent 15 16 BD0036 17
64

What I would like for it to look like:

Prod Code Item Name Location Qty Bin 1 Bin 2
BD0036 Oxygen 10 2 17 64
BD0100 Vent 15 16 25 42

I have 20,000 rows and would appreciate any kind of help i can get

 

[Scott]

do you have 20 000 rows in both columns? An easy way would be to use a
vlookup function. But that might take a little bit of time if you have 20
000. If you sorted them then this would make things faster. Or you can
create a find function that will only search the one column (G) with data
from the first column (A). When this is found it could return the two values
needed.

Another thing, does the product code in the first column always have a match
in the second column?

Anyways before you do anything, try sorting it. Because a lot of the
functions will work faster with pre sorted data.

Cheers,
Scott

 

[WLMPilot]

Looking at what your data looks like now, I noticed that the PROD CODE in
column G does not match the PROD CODE in column A. As long as you do not
have duplicate product codes in G, I would sort G accordingly so that the
product code in G will match that in A. Then I would delete column G. This
should bring everything in line, again, providing there are no duplicates nor
empty cells.

 

[genesisoxygen]

do you have 20 000 rows in both columns? An easy way would be to use a
vlookup function. But that might take a little bit of time if you have 20
000. If you sorted them then this would make things faster. Or you can
create a find function that will only search the one column (G) with data
from the first column (A). When this is found it could return the two values
needed.

Another thing, does the product code in the first column always have a match
in the second column?

Anyways before you do anything, try sorting it. Because a lot of the
functions will work faster with pre sorted data.

Cheers,
Scott

No in most cases it does not have a match. Column (A) might have 5,000
product codes with the same product code apearing muliple times
depending on what locations carry that product and column (G) will have
20,000 unique product codes only appearing once.

 

[genesisoxygen]

No in most cases it does not have a match. Column (A) might have 5,000
product codes with the same product code apearing muliple times
depending on what locations carry that product and column (G) will have
20,000 unique product codes only appearing once.

create a find function that will only search the one column (G) with
data
from the first column (A). When this is found it could return the two
values
needed.

Could you Elaborate on this a little bit for i am but a rookie

 

[Scott]

Here is an example of what I've used. It's for something similar to what
you're doing. if you can't make heads or tails of it I'll explain it more
later.

sub test
dim tn as long, num as long, i as long, a(1 to 5000, 1 to 5000, 1 to 5000),
' for the following count the smaller of the two ranges and input them into
the array a(i)
tn = Excel.WorksheetFunction.CountA(Range("AT:AT")) - 1 ' <----- change this

range("At1").select
For i = 1 To tn
If ActiveCell.Offset(0, 1) = "" Then
Range(ActiveCell, ActiveCell.Offset(0, 1)).Delete 'in case of blank
cells
i = i - 1
Else
a(1,i) = ActiveCell.Offset(i - 1, 0)
a(2,i) = ActiveCell.Offset(i - 1, 1)
a(3,i) = ActiveCell.Offset(i - 1, 2)
End If
Next i

For i = 1 To tn
Set rng = FindStuff(a(i))
If Not rng Is Nothing Then
rng.Activate
activecell.offset(0,1) = a(2,i) ' <----- change these (just the
offset)
activecell.offset(0,2) = a(3,i)
End If
Next i
end sub


''''' this is the function getstuff

Public Function FindStuff(ByVal strTofind As String) As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet

Set wksToSearch = Sheets("Data") '''' <------ change this
With wksToSearch
Set rngToSearch = .Range(.Range("B2"), .Cells(Rows.Count,
"B").End(xlUp)) '''' <------ change this
End With
Set FindStuff = rngToSearch.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
End Function

cheers,
Scott

 

[genesisoxygen]

Here is an example of what I've used. It's for something similar to what
you're doing. if you can't make heads or tails of it I'll explain it more
later.

sub test
dim tn as long, num as long, i as long, a(1 to 5000, 1 to 5000, 1 to 5000),
' for the following count the smaller of the two ranges and input them into
the array a(i)
tn = Excel.WorksheetFunction.CountA(Range("AT:AT")) - 1 ' <----- change this

range("At1").select
For i = 1 To tn
If ActiveCell.Offset(0, 1) = "" Then
Range(ActiveCell, ActiveCell.Offset(0, 1)).Delete 'in case of blank
cells
i = i - 1
Else
a(1,i) = ActiveCell.Offset(i - 1, 0)
a(2,i) = ActiveCell.Offset(i - 1, 1)
a(3,i) = ActiveCell.Offset(i - 1, 2)
End If
Next i

For i = 1 To tn
Set rng = FindStuff(a(i))
If Not rng Is Nothing Then
rng.Activate
activecell.offset(0,1) = a(2,i) ' <----- change these (just the
offset)
activecell.offset(0,2) = a(3,i)
End If
Next i
end sub


''''' this is the function getstuff

Public Function FindStuff(ByVal strTofind As String) As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet

Set wksToSearch = Sheets("Data") '''' <------ change this
With wksToSearch
Set rngToSearch = .Range(.Range("B2"), .Cells(Rows.Count,
"B").End(xlUp)) '''' <------ change this
End With
Set FindStuff = rngToSearch.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
End Function

cheers,
Scott

I have no clue to what this means is there a more simple way? I know
how to insert a macros but have no clue on writing one.

 

[Scott]

ok. well are you willing to try using a macro? I should be able to help you
through it. let me get the following straight, column A is product code, B
is item name... E is product code, f is bin 1 and g is bin 2?
if this is the case, try using the following. Just change the name of the
sheet where I mention it and hopefully this will work. Just make sure you
back up your file before trying anything. (if there's any errors just
mention them and I'll see if I can figure it out).

(it is possible to do it without a macro, but this will probably be the
fastest alternative)

cheers,
Scott

sub test
'this first part is just defining the variables
dim tn as long, num as long, i as long, a(1 to 5000, 1 to 5000, 1 to 5000)

'tn is the count of the number of items we will be looking up
tn = Excel.WorksheetFunction.CountA(Range("E:E")) - 1


'the following takes all the items and puts them into the array a(i..)
range("E1").select

'this is the loop that goes through all the items
For i = 1 To tn
'in case the activecell offset (i-1 rows down) is blank then it deletes the
range
If ActiveCell.Offset(i-1, 0) = "" Then
Range(ActiveCell(i-1,0), ActiveCell.Offset(i-1, 3)).Delete
i = i - 1
Else
'if it is not blank it is read into the array
a(1,i) = ActiveCell.Offset(i - 1, 0)
a(2,i) = ActiveCell.Offset(i - 1, 1)
a(3,i) = ActiveCell.Offset(i - 1, 2)
End If
Next i

'now we'll see if we can find the items in the other range
For i = 1 To tn
'this calls the function findstuff and passes it the variable from the array
Set rng = FindStuff(a(1,i))
'if the rng returned is not nothing (there was something found) then the
following gets done
If Not rng Is Nothing Then
rng.Activate
activecell.offset(0,4) = a(2,i)
activecell.offset(0,5) = a(3,i)
End If
Next i
end sub


Public Function FindStuff(ByVal strTofind As String) As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet

Set wksToSearch = Sheets("Data") ' <---- here just put in your sheet name
With wksToSearch
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count,
"A").End(xlUp))
End With
Set FindStuff = rngToSearch.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
End Function

 

[genesisoxygen]

ok. well are you willing to try using a macro? I should be able to help you
through it. let me get the following straight, column A is product code, B
is item name... E is product code, f is bin 1 and g is bin 2?
if this is the case, try using the following. Just change the name of the
sheet where I mention it and hopefully this will work. Just make sure you
back up your file before trying anything. (if there's any errors just
mention them and I'll see if I can figure it out).

(it is possible to do it without a macro, but this will probably be the
fastest alternative)

cheers,
Scott

sub test
'this first part is just defining the variables
dim tn as long, num as long, i as long, a(1 to 5000, 1 to 5000, 1 to 5000)

'tn is the count of the number of items we will be looking up
tn = Excel.WorksheetFunction.CountA(Range("E:E")) - 1


'the following takes all the items and puts them into the array a(i..)
range("E1").select

'this is the loop that goes through all the items
For i = 1 To tn
'in case the activecell offset (i-1 rows down) is blank then it deletes the
range
If ActiveCell.Offset(i-1, 0) = "" Then
Range(ActiveCell(i-1,0), ActiveCell.Offset(i-1, 3)).Delete
i = i - 1
Else
'if it is not blank it is read into the array
a(1,i) = ActiveCell.Offset(i - 1, 0)
a(2,i) = ActiveCell.Offset(i - 1, 1)
a(3,i) = ActiveCell.Offset(i - 1, 2)
End If
Next i

'now we'll see if we can find the items in the other range
For i = 1 To tn
'this calls the function findstuff and passes it the variable from the array
Set rng = FindStuff(a(1,i))
'if the rng returned is not nothing (there was something found) then the
following gets done
If Not rng Is Nothing Then
rng.Activate
activecell.offset(0,4) = a(2,i)
activecell.offset(0,5) = a(3,i)
End If
Next i
end sub


Public Function FindStuff(ByVal strTofind As String) As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet

Set wksToSearch = Sheets("Data") ' <---- here just put in your sheet name
With wksToSearch
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count,
"A").End(xlUp))
End With
Set FindStuff = rngToSearch.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
End Function

Did what you said and put in the name of my sheet (movement form) then
when i ran it i got a error message that read "cant execute code in
break mode"

 

[genesisoxygen]

Did what you said and put in the name of my sheet (movement form) then
when i ran it i got a error message that read "cant execute code in
break mode"

Maybe this is why it didnt work
column A is product code, B is item name, C is location name, D is
Reorder Qty E and F is blank, G is product code, H is bin 1, i is bin
2. I need bin 1 to appear in E and bin 2 to appear in F

 

[Scott]

Hello,

For the break mode error that just means that you tried running the sub, it
probably failed (or you set a break point) and then you tried to run it
again. To avoid this, just make sure you reset whenever the program stops
for some reason (in the VBA editor click on "Run" and then "Reset".

Just another thing, in VBA just make sure that there isn't any red code
(because of the import from this to VBA some of the comments may be red, this
could also be causing an error.

Here I made a couple of modifications to the code (make it a bit faster and
with the information you gave me (col e and f are blank)). This seems to run
fine on my computer, if you still have any problems just post em. (again
just make sure there isn't any red code when you put this in VBA)

Sub test()
'this first part is just defining the variables
Dim tn As Long, num As Long, i As Long, a(1 To 6000, 1 To 3), rng As Range

'tn is the count of the number of items we will be looking up
tn = Excel.WorksheetFunction.CountA(Range("G:G")) - 1


'the following takes all the items and puts them into the array a(i..)
Range("G2").Select

'this is the loop that goes through all the items
For i = 1 To tn
'in case the activecell offset (i-1 rows down) is blank then it deletes the
Range
If ActiveCell.Offset(i - 1, 0) = "" Then
Range(ActiveCell(i - 1, 0), ActiveCell.Offset(i - 1, 3)).Delete
i = i - 1
Else
'if it is not blank it is read into the array
a(i, 1) = ActiveCell.Offset(i - 1, 0)
a(i, 2) = ActiveCell.Offset(i - 1, 1)
a(i, 3) = ActiveCell.Offset(i - 1, 2)
End If
Next i

'now we'll see if we can find the items in the other range
For i = 1 To tn
'this calls the function findstuff and passes it the variable from the array
Set rng = FindStuff(a(i, 1))
'if the rng returned is not nothing (there was something found) then
thefollowing gets done
If Not rng Is Nothing Then
rng.Activate
ActiveCell.Offset(0, 4) = a(i, 2)
ActiveCell.Offset(0, 5) = a(i, 3)
End If
Next i
End Sub

Public Function FindStuff(ByVal strTofind As String) As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet

Set wksToSearch = Sheets("movement form") ' <---- here just put in your
sheet name
With wksToSearch
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
End With
Set FindStuff = rngToSearch.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
End Function



Cheers,

Scott

 

[genesisoxygen]

Hello,

For the break mode error that just means that you tried running the sub, it
probably failed (or you set a break point) and then you tried to run it
again. To avoid this, just make sure you reset whenever the program stops
for some reason (in the VBA editor click on "Run" and then "Reset".

Just another thing, in VBA just make sure that there isn't any red code
(because of the import from this to VBA some of the comments may be red, this
could also be causing an error.

Here I made a couple of modifications to the code (make it a bit faster and
with the information you gave me (col e and f are blank)). This seems to run
fine on my computer, if you still have any problems just post em. (again
just make sure there isn't any red code when you put this in VBA)

Sub test()
'this first part is just defining the variables
Dim tn As Long, num As Long, i As Long, a(1 To 6000, 1 To 3), rng As Range

'tn is the count of the number of items we will be looking up
tn = Excel.WorksheetFunction.CountA(Range("G:G")) - 1


'the following takes all the items and puts them into the array a(i..)
Range("G2").Select

'this is the loop that goes through all the items
For i = 1 To tn
'in case the activecell offset (i-1 rows down) is blank then it deletes the
Range
If ActiveCell.Offset(i - 1, 0) = "" Then
Range(ActiveCell(i - 1, 0), ActiveCell.Offset(i - 1, 3)).Delete
i = i - 1
Else
'if it is not blank it is read into the array
a(i, 1) = ActiveCell.Offset(i - 1, 0)
a(i, 2) = ActiveCell.Offset(i - 1, 1)
a(i, 3) = ActiveCell.Offset(i - 1, 2)
End If
Next i

'now we'll see if we can find the items in the other range
For i = 1 To tn
'this calls the function findstuff and passes it the variable from the array
Set rng = FindStuff(a(i, 1))
'if the rng returned is not nothing (there was something found) then
thefollowing gets done
If Not rng Is Nothing Then
rng.Activate
ActiveCell.Offset(0, 4) = a(i, 2)
ActiveCell.Offset(0, 5) = a(i, 3)
End If
Next i
End Sub

Public Function FindStuff(ByVal strTofind As String) As Range
Dim rngToSearch As Range
Dim wksToSearch As Worksheet

Set wksToSearch = Sheets("movement form") ' <---- here just put in your
sheet name
With wksToSearch
Set rngToSearch = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
End With
Set FindStuff = rngToSearch.Find(What:=strTofind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
End Function



Cheers,

Scott

Thank you sir it works Flawlessly, i really appreciate what youve done,
thanks again

 

[Scott]

No problem, glad I could help.

Cheers,

Scott