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

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

" wrote:

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 wrote:
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

" wrote:

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




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.

wrote:
Scott wrote:
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

" wrote:

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




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

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
" wrote:


wrote:
Scott wrote:
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

" wrote:

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




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 wrote:
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
" wrote:


wrote:
Scott wrote:
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

" wrote:

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




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


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.

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.



" wrote:

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