Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Column lookups of specified numbers
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Column lookups of specified numbers

I have two columns of Work Order Numbers, A & C

A B C
WO000023 WO000002
WO000027 WO000003
WO000029 WO000004
WO000084 WO000010
WO000086 WO000011
WO000099 WO000012
WO000101 WO000013
WO000115 WO000014
WO000153 WO000020
WO000164 WO000021
WO000165 WO000024
WO000175 WO000026
WO000176 WO000028
WO000185 WO000030
WO000189 WO000033
WO000190 WO000035
WO000192 WO000036
WO000199 WO000038
WO000203 WO000039
WO000209 WO000040
WO000214 WO000041
.... ...
Column A represents numbers that are already used; C consists of unused
numbers.
I want to create a new WO number for each value in A and place it (in column
B) beside the corresponding number in A. It has to be a number from column C
that is higher than the one in A. I then want to delete the value used from
column C.

Any ideas on how to handle the logic?

Here's a start...
Dim i,j as Integer

for i=1 to 35000
'locate a number (eg. Cells(j,3) in column c that is greater than
Cells(i,1).value
???
Cells(i,2).value =Cells(j,3).value
Cells(j,3).value = ""


next
end

Any help in suggesting how to replace the ??? with code that will work, will
be appreciated immensely.

Thanks,
Jim Berglund


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Column lookups of specified numbers
Jim,

I'm struggling to understand the question. If we are to pick a number from
Col C and put it adjacent to a lower number in Col A then there aren't enough
numbers in Col C as there are only 11 numbers that are higher than those in
Col A. What is to methodology for creating more numbers?

Mike

"Jim Berglund" wrote:


I have two columns of Work Order Numbers, A & C

A B C
WO000023 WO000002
WO000027 WO000003
WO000029 WO000004
WO000084 WO000010
WO000086 WO000011
WO000099 WO000012
WO000101 WO000013
WO000115 WO000014
WO000153 WO000020
WO000164 WO000021
WO000165 WO000024
WO000175 WO000026
WO000176 WO000028
WO000185 WO000030
WO000189 WO000033
WO000190 WO000035
WO000192 WO000036
WO000199 WO000038
WO000203 WO000039
WO000209 WO000040
WO000214 WO000041
.... ...
Column A represents numbers that are already used; C consists of unused
numbers.
I want to create a new WO number for each value in A and place it (in column
B) beside the corresponding number in A. It has to be a number from column C
that is higher than the one in A. I then want to delete the value used from
column C.

Any ideas on how to handle the logic?

Here's a start...
Dim i,j as Integer

for i=1 to 35000
'locate a number (eg. Cells(j,3) in column c that is greater than
Cells(i,1).value
???
Cells(i,2).value =Cells(j,3).value
Cells(j,3).value = ""


next
end

Any help in suggesting how to replace the ??? with code that will work, will
be appreciated immensely.

Thanks,
Jim Berglund



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Column lookups of specified numbers
There are actually 25,000 numbers or so in Col C. I want to pick 'the next'
higher number from column C. For example,



Th efirst number in column A is WO000023. Look for a number in column C that
is larger - in this case, WO000024,and put it in Cell B1. Then delete the
value WO000024 from Column C and cycle through the rest of the numbers in
Column A.



Since we have just deleted a number from Column C, we should restart the
lookup at the next nember in Column C - not start from the beginning



Thanks,

Jim

"Mike H" wrote in message
...
Jim,

I'm struggling to understand the question. If we are to pick a number from
Col C and put it adjacent to a lower number in Col A then there aren't
enough
numbers in Col C as there are only 11 numbers that are higher than those
in
Col A. What is to methodology for creating more numbers?

Mike

"Jim Berglund" wrote:


I have two columns of Work Order Numbers, A & C

A B C
WO000023 WO000002
WO000027 WO000003
WO000029 WO000004
WO000084 WO000010
WO000086 WO000011
WO000099 WO000012
WO000101 WO000013
WO000115 WO000014
WO000153 WO000020
WO000164 WO000021
WO000165 WO000024
WO000175 WO000026
WO000176 WO000028
WO000185 WO000030
WO000189 WO000033
WO000190 WO000035
WO000192 WO000036
WO000199 WO000038
WO000203 WO000039
WO000209 WO000040
WO000214 WO000041
.... ...
Column A represents numbers that are already used; C consists of unused
numbers.
I want to create a new WO number for each value in A and place it (in
column
B) beside the corresponding number in A. It has to be a number from
column C
that is higher than the one in A. I then want to delete the value used
from
column C.

Any ideas on how to handle the logic?

Here's a start...
Dim i,j as Integer

for i=1 to 35000
'locate a number (eg. Cells(j,3) in column c that is greater than
Cells(i,1).value
???
Cells(i,2).value =Cells(j,3).value
Cells(j,3).value = ""


next
end

Any help in suggesting how to replace the ??? with code that will work,
will
be appreciated immensely.

Thanks,
Jim Berglund





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default Column lookups of specified numbers
Jim,

Give this a try:


Sub test()

Dim lCurNumber As Long
Dim lNewNumber As Long
Dim lRow1 As Long
Dim lRow2 As Long

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

lRow1 = 1
lRow2 = 1
While Range("A" & lRow1).Value < ""
lCurNumber = CLng(Mid(Range("A" & lRow1), 3))
lNewNumber = CLng(Mid(Range("C" & lRow1), 3))
While lCurNumber = lNewNumber
lRow2 = lRow2 + 1
With Range("C" & lRow2)
If .Text < "" Then
lNewNumber = CLng(Mid(.Value, 3))
End If
End With
If lRow2 = 65535 Then
MsgBox "No more Order Numbers!"
Exit Sub
End If
Wend
Range("B" & lRow1).Value = "WO" & Format(lNewNumber, "000000")
Range("C" & lRow2).Delete shift:=xlUp
lRow1 = lRow1 + 1
lRow2 = lRow2 + 1
Wend

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


--
Hope that helps.

Vergel Adriano


"Jim Berglund" wrote:

There are actually 25,000 numbers or so in Col C. I want to pick 'the next'
higher number from column C. For example,



Th efirst number in column A is WO000023. Look for a number in column C that
is larger - in this case, WO000024,and put it in Cell B1. Then delete the
value WO000024 from Column C and cycle through the rest of the numbers in
Column A.



Since we have just deleted a number from Column C, we should restart the
lookup at the next nember in Column C - not start from the beginning



Thanks,

Jim

"Mike H" wrote in message
...
Jim,

I'm struggling to understand the question. If we are to pick a number from
Col C and put it adjacent to a lower number in Col A then there aren't
enough
numbers in Col C as there are only 11 numbers that are higher than those
in
Col A. What is to methodology for creating more numbers?

Mike

"Jim Berglund" wrote:


I have two columns of Work Order Numbers, A & C

A B C
WO000023 WO000002
WO000027 WO000003
WO000029 WO000004
WO000084 WO000010
WO000086 WO000011
WO000099 WO000012
WO000101 WO000013
WO000115 WO000014
WO000153 WO000020
WO000164 WO000021
WO000165 WO000024
WO000175 WO000026
WO000176 WO000028
WO000185 WO000030
WO000189 WO000033
WO000190 WO000035
WO000192 WO000036
WO000199 WO000038
WO000203 WO000039
WO000209 WO000040
WO000214 WO000041
.... ...
Column A represents numbers that are already used; C consists of unused
numbers.
I want to create a new WO number for each value in A and place it (in
column
B) beside the corresponding number in A. It has to be a number from
column C
that is higher than the one in A. I then want to delete the value used
from
column C.

Any ideas on how to handle the logic?

Here's a start...
Dim i,j as Integer

for i=1 to 35000
'locate a number (eg. Cells(j,3) in column c that is greater than
Cells(i,1).value
???
Cells(i,2).value =Cells(j,3).value
Cells(j,3).value = ""


next
end

Any help in suggesting how to replace the ??? with code that will work,
will
be appreciated immensely.

Thanks,
Jim Berglund






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default Column lookups of specified numbers
I found some problems with the first suggestion.. Try this instead:


Sub test()

Dim lCurNumber As Long
Dim lNewNumber As Long
Dim lRow1 As Long
Dim lRow2 As Long

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

lRow1 = 1
lRow2 = 1
While Range("A" & lRow1).Value < ""
lCurNumber = CLng(Mid(Range("A" & lRow1), 3))
lNewNumber = CLng(Mid(Range("C" & lRow2), 3))
While lCurNumber = lNewNumber
lRow2 = lRow2 + 1
With Range("C" & lRow2)
If .Text < "" Then
lNewNumber = CLng(Mid(.Value, 3))
End If
End With
If lRow2 = 65535 Then
MsgBox "No more Order Numbers!"
Exit Sub
End If
Wend
Range("B" & lRow1).Value = "WO" & Format(lNewNumber, "000000")
Range("C" & lRow2).Delete shift:=xlUp
lRow1 = lRow1 + 1
Wend

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Jim,

Give this a try:


Sub test()

Dim lCurNumber As Long
Dim lNewNumber As Long
Dim lRow1 As Long
Dim lRow2 As Long

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

lRow1 = 1
lRow2 = 1
While Range("A" & lRow1).Value < ""
lCurNumber = CLng(Mid(Range("A" & lRow1), 3))
lNewNumber = CLng(Mid(Range("C" & lRow1), 3))
While lCurNumber = lNewNumber
lRow2 = lRow2 + 1
With Range("C" & lRow2)
If .Text < "" Then
lNewNumber = CLng(Mid(.Value, 3))
End If
End With
If lRow2 = 65535 Then
MsgBox "No more Order Numbers!"
Exit Sub
End If
Wend
Range("B" & lRow1).Value = "WO" & Format(lNewNumber, "000000")
Range("C" & lRow2).Delete shift:=xlUp
lRow1 = lRow1 + 1
lRow2 = lRow2 + 1
Wend

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


--
Hope that helps.

Vergel Adriano


"Jim Berglund" wrote:

There are actually 25,000 numbers or so in Col C. I want to pick 'the next'
higher number from column C. For example,



Th efirst number in column A is WO000023. Look for a number in column C that
is larger - in this case, WO000024,and put it in Cell B1. Then delete the
value WO000024 from Column C and cycle through the rest of the numbers in
Column A.



Since we have just deleted a number from Column C, we should restart the
lookup at the next nember in Column C - not start from the beginning



Thanks,

Jim

"Mike H" wrote in message
...
Jim,

I'm struggling to understand the question. If we are to pick a number from
Col C and put it adjacent to a lower number in Col A then there aren't
enough
numbers in Col C as there are only 11 numbers that are higher than those
in
Col A. What is to methodology for creating more numbers?

Mike

"Jim Berglund" wrote:


I have two columns of Work Order Numbers, A & C

A B C
WO000023 WO000002
WO000027 WO000003
WO000029 WO000004
WO000084 WO000010
WO000086 WO000011
WO000099 WO000012
WO000101 WO000013
WO000115 WO000014
WO000153 WO000020
WO000164 WO000021
WO000165 WO000024
WO000175 WO000026
WO000176 WO000028
WO000185 WO000030
WO000189 WO000033
WO000190 WO000035
WO000192 WO000036
WO000199 WO000038
WO000203 WO000039
WO000209 WO000040
WO000214 WO000041
.... ...
Column A represents numbers that are already used; C consists of unused
numbers.
I want to create a new WO number for each value in A and place it (in
column
B) beside the corresponding number in A. It has to be a number from
column C
that is higher than the one in A. I then want to delete the value used
from
column C.

Any ideas on how to handle the logic?

Here's a start...
Dim i,j as Integer

for i=1 to 35000
'locate a number (eg. Cells(j,3) in column c that is greater than
Cells(i,1).value
???
Cells(i,2).value =Cells(j,3).value
Cells(j,3).value = ""


next
end

Any help in suggesting how to replace the ??? with code that will work,
will
be appreciated immensely.

Thanks,
Jim Berglund






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Column lookups of specified numbers
I hadn't heard from anyone and took a more direct approach. Yours looks far
more elegant, but I don't understand a lot of it. (I hope to when I go
through it in detail.)

Here's what I ended up doing...

Sub HKY() 'Find higher WO Number
Dim i, j As Integer

j = 1
With ActiveSheet
For i = 1 To 25000
If .Cells(i, 1).Value = "" Then
GoTo Finish:
End If
While .Cells(1, 14).Value < .Cells(i, 1).Value
.Cells(1, 14).Select
Selection.Copy
.Cells(j, 16).Select
.Paste
.Cells(1, 14).Select
Selection.Delete Shift:=xlUp
j = j + 1
Wend
.Cells(i, 11).Value = .Cells(1, 14).Value
.Cells(1, 14).Select
Selection.Delete Shift:=xlUp
Next

Finish:
End With
End Sub

Thanks for the work you did on it.
Jim Berglund

"Vergel Adriano" wrote in message
...
I found some problems with the first suggestion.. Try this instead:


Sub test()

Dim lCurNumber As Long
Dim lNewNumber As Long
Dim lRow1 As Long
Dim lRow2 As Long

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

lRow1 = 1
lRow2 = 1
While Range("A" & lRow1).Value < ""
lCurNumber = CLng(Mid(Range("A" & lRow1), 3))
lNewNumber = CLng(Mid(Range("C" & lRow2), 3))
While lCurNumber = lNewNumber
lRow2 = lRow2 + 1
With Range("C" & lRow2)
If .Text < "" Then
lNewNumber = CLng(Mid(.Value, 3))
End If
End With
If lRow2 = 65535 Then
MsgBox "No more Order Numbers!"
Exit Sub
End If
Wend
Range("B" & lRow1).Value = "WO" & Format(lNewNumber, "000000")
Range("C" & lRow2).Delete shift:=xlUp
lRow1 = lRow1 + 1
Wend

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Jim,

Give this a try:


Sub test()

Dim lCurNumber As Long
Dim lNewNumber As Long
Dim lRow1 As Long
Dim lRow2 As Long

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

lRow1 = 1
lRow2 = 1
While Range("A" & lRow1).Value < ""
lCurNumber = CLng(Mid(Range("A" & lRow1), 3))
lNewNumber = CLng(Mid(Range("C" & lRow1), 3))
While lCurNumber = lNewNumber
lRow2 = lRow2 + 1
With Range("C" & lRow2)
If .Text < "" Then
lNewNumber = CLng(Mid(.Value, 3))
End If
End With
If lRow2 = 65535 Then
MsgBox "No more Order Numbers!"
Exit Sub
End If
Wend
Range("B" & lRow1).Value = "WO" & Format(lNewNumber, "000000")
Range("C" & lRow2).Delete shift:=xlUp
lRow1 = lRow1 + 1
lRow2 = lRow2 + 1
Wend

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


--
Hope that helps.

Vergel Adriano


"Jim Berglund" wrote:

There are actually 25,000 numbers or so in Col C. I want to pick 'the
next'
higher number from column C. For example,



Th efirst number in column A is WO000023. Look for a number in column C
that
is larger - in this case, WO000024,and put it in Cell B1. Then delete
the
value WO000024 from Column C and cycle through the rest of the numbers
in
Column A.



Since we have just deleted a number from Column C, we should restart
the
lookup at the next nember in Column C - not start from the beginning



Thanks,

Jim

"Mike H" wrote in message
...
Jim,

I'm struggling to understand the question. If we are to pick a number
from
Col C and put it adjacent to a lower number in Col A then there
aren't
enough
numbers in Col C as there are only 11 numbers that are higher than
those
in
Col A. What is to methodology for creating more numbers?

Mike

"Jim Berglund" wrote:


I have two columns of Work Order Numbers, A & C

A B C
WO000023 WO000002
WO000027 WO000003
WO000029 WO000004
WO000084 WO000010
WO000086 WO000011
WO000099 WO000012
WO000101 WO000013
WO000115 WO000014
WO000153 WO000020
WO000164 WO000021
WO000165 WO000024
WO000175 WO000026
WO000176 WO000028
WO000185 WO000030
WO000189 WO000033
WO000190 WO000035
WO000192 WO000036
WO000199 WO000038
WO000203 WO000039
WO000209 WO000040
WO000214 WO000041
.... ...
Column A represents numbers that are already used; C consists of
unused
numbers.
I want to create a new WO number for each value in A and place it
(in
column
B) beside the corresponding number in A. It has to be a number from
column C
that is higher than the one in A. I then want to delete the value
used
from
column C.

Any ideas on how to handle the logic?

Here's a start...
Dim i,j as Integer

for i=1 to 35000
'locate a number (eg. Cells(j,3) in column c that is greater than
Cells(i,1).value
???
Cells(i,2).value =Cells(j,3).value
Cells(j,3).value = ""


next
end

Any help in suggesting how to replace the ??? with code that will
work,
will
be appreciated immensely.

Thanks,
Jim Berglund








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
lookups based on more than one column childothe1980s Excel Programming 4 February 8th 06 02:54 PM
multiple column lookups Mark B Excel Worksheet Functions 1 February 6th 06 09:58 PM
multiple column lookups Kevin Vaughn Excel Worksheet Functions 0 February 6th 06 09:36 PM
multiple column lookups Mark B Excel Worksheet Functions 0 February 6th 06 09:29 PM
multiple column lookups Kevin Vaughn Excel Worksheet Functions 0 February 6th 06 09:28 PM


All times are GMT +1. The time now is 09:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement