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

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

"[email protected]" 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
>
>

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.



"[email protected]" 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
>
> "[email protected]" 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.

[email protected] 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
> >
> > "[email protected]" 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
"[email protected]" wrote:

>
> [email protected] 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
> > >
> > > "[email protected]" 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
> "[email protected]" wrote:
>
> >
> > [email protected] 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
> > > >
> > > > "[email protected]" 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.

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


"[email protected]" wrote:

>
> 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
> > "[email protected]" wrote:
> >
> > >
> > > [email protected] 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
> > > > >
> > > > > "[email protected]" 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.
>
>

Scott wrote:
> 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
>
>
> "[email protected]" wrote:
>
> >
> > 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
> > > "[email protected]" wrote:
> > >
> > > >
> > > > [email protected] 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
> > > > > >
> > > > > > "[email protected]" 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.
> >
> >
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"

[email protected] wrote:
> Scott wrote:
> > 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
> >
> >
> > "[email protected]" wrote:
> >
> > >
> > > 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
> > > > "[email protected]" wrote:
> > > >
> > > > >
> > > > > [email protected] 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
> > > > > > >
> > > > > > > "[email protected]" 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.
> > >
> > >
> 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

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





"[email protected]" wrote:

>
> [email protected] wrote:
> > Scott wrote:
> > > 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
> > >
> > >
> > > "[email protected]" wrote:
> > >
> > > >
> > > > 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
> > > > > "[email protected]" wrote:
> > > > >
> > > > > >
> > > > > > [email protected] 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
> > > > > > > >
> > > > > > > > "[email protected]" 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.
> > > >
> > > >
> > 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 wrote:
> 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
>
>
>
>
>
> "[email protected]" wrote:
>
> >
> > [email protected] wrote:
> > > Scott wrote:
> > > > 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
> > > >
> > > >
> > > > "[email protected]" wrote:
> > > >
> > > > >
> > > > > 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
> > > > > > "[email protected]" wrote:
> > > > > >
> > > > > > >
> > > > > > > [email protected] 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
> > > > > > > > >
> > > > > > > > > "[email protected]" 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.
> > > > >
> > > > >
> > > 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
> >
> >
Thank you sir it works Flawlessly, i really appreciate what youve done,
thanks again

No problem, glad I could help.

Cheers,

Scott

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