Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes howev
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes howev
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes howev
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes howev
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes ho
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes ho
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 " 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 " 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes ho
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 " 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 " 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. 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" |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes ho
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 " 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 " 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. 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes ho
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 " wrote: 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 " 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 " 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. 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes ho
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 " wrote: 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 " 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 " 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. 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have two columns (A) and (G) that have like product codes ho
No problem, glad I could help.
Cheers, Scott Thank you sir it works Flawlessly, i really appreciate what youve done, thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Querying multiple Product Codes in one field using SUMPRODUCT() | New Users to Excel | |||
I have two columns (A) and (G) that have like product codes however | Excel Worksheet Functions | |||
Sum Product if? Three columns | Excel Worksheet Functions | |||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C | Excel Programming | |||
Sum product of many pairs of columns | Excel Worksheet Functions |