Need some help with a difficult loop 2

[nathanstevenson]

Hi there,

Here is the problem:
I have to give unique IDs to 9 different sets of data stored
in 15 different columns in an Excel file.

So I need a loop that will number cell B1 - 1 and put it in cell A1 (i.e. the column to store the unique ID). It needs to then take cell B1 and compare it to cells B2-B1000. If B1 equals any other cells in this range, then it's corresponsing "A" cell needs to be numbered the same as B1. Once this is done for the whole range, it is then repeated using cell B2, and so on until it is done.

The trick comes in when I have to compare two or more columns to others. i.e. b1 and c1 need to be compared to b2 and c2 etc.

My main problem is how I store the values from the cells. Do I use named ranges? If so how? How do I refer to col a1 (i.e. the ID col), ito variables as opposed to fixed locations?

This loop also needs to be repeated a couple of times on dif data.

 

[mwolf00]

sub checkCols()
dim bVal as String
dim cVal as String
dim rowNum as Integer
dim i as Integer
dim j as Integer
dim IDnumber as Integer

i = 1
rowNum = 1
IDnumber = 1

bVal = cells(i, 2).value
cVal = cells(i, 3).value

for i = 1 to 1000
for j = rowNum to 1000
if cells(j, 2).value = bVal AND cells(j,3) = cVal then
cells(j,1).value = IDnumber
end if
next
if i < 1000
if cells(i+1,1) = 0 then
bVal = cells(i, 2).value
cVal = cells(i, 3).value
else
do while i <= 1000
i = i + 1
if cells(i,0) = 0 THEN
bVal = cells(i, 2).value
cVal = cells(i, 3).value
exit do
end if
loop
end if
idNumber = IDnumber + 1
end if
next Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[mwolf00]

OOPPS!

sub checkCols()
dim bVal as String
dim cVal as String
dim rowNum as Integer
dim i as Integer
dim j as Integer
dim IDnumber as Integer

i = 1
rowNum = 1
IDnumber = 1

bVal = cells(i, 2).value
cVal = cells(i, 3).value

for i = 1 to 1000
for j = rowNum i to 1000
if cells(j, 2).value = bVal AND cells(j,3) = cVal then
cells(j,1).value = IDnumber
end if
next
if i < 1000
if cells(i+1,1) = 0 then
bVal = cells(i, 2).value
cVal = cells(i, 3).value
else
do while i <= 1000
i = i + 1
if cells(i,0) = 0 THEN
bVal = cells(i, 2).value
cVal = cells(i, 3).value
exit do
end if
loop
end if
idNumber = IDnumber + 1
end if
next Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[RobBroekhuis]

Just out of curiosity, I tried to do what I think you're trying to do in Excel formulas, rather than VBA. The following works: If your column values are in column A, starting in row 1, you can put your value ID in column B by putting 1 into B1, and the formula

=IF(ISERROR(VLOOKUP(B2,$B$1:B1,1,FALSE)),MAX($C$1:C1)+1,VLOOKUP(B2,$B$1:C1,2,FALSE))

in all the other B-column cells. This assigns an incrementing ID to unique A-column values. You could put these formulas in programmatically, and do a copy - paste special as values to convert the formulas to constants.

Rob

 

[nathanstevenson]

Hi there Mike,

How would I implement this code cause there are no cell references. i.e. how Excel know which cells are which?

I also just tried to run the code, it said syntax error, at the If 1 < 1000 place.

Please could you explain the code a bit so that I can understand the logic behind it.

Thanks!
Nathan

 

[mwolf00]

That should read:
if i < 1000 THEN
Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[nathanstevenson]

Rob:

This looks promising, but it seems to only compare the first set of values to all the others, whereas it needs to compare the i.val for i to X, then i.val +1 for i+1 to X,
and so on till i =X.

Any suggestions?

 

[mwolf00]

sub checkCols()
dim bVal as String
dim cVal as String
dim i as Integer
dim j as Integer
dim IDnumber as Integer

i = 1
IDnumber = 1

bVal = cells(i, 2).value Puts value of cell B1 into variable bVal
cVal = cells(i, 3).value Puts value of cell C1 into variable cVal

for i = 1 to 1000 If you have vals in rows 1 to 1000
for j = i to 1000 look at all of the remaining rows
if cells(j, 2).value = bVal AND cells(j,3) = cVal then
cells(j,1).value = IDnumber if B and C columns match, give it the current ID
end if
next
if i < 1000 If not, your done anyways
if cells(i+1,1) = 0 then See if the next cell has been identified as matching a higher row - if 0 then it has not
bVal = cells(i, 2).value
cVal = cells(i, 3).value
else find the next row where b and c have NOT been matched
do while i <= 1000
i = i + 1 THEN
if cells(i,0) = 0 THEN
bVal = cells(i, 2).value
cVal = cells(i, 3).value
exit do
end if
loop
end if
idNumber = IDnumber + 1 increment your id
end if
next Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[RobBroekhuis]

To do it in formulas, you'd have to create a (temporary) additional column that has the concatenated values of the B and C columns, e.g.

in column D: =concatenate(B1,&quot;#&quot;,C1)

and then run the same formulas as above in column E (changing, of course, A to D, and B to E)

Rob

 

[nathanstevenson]

Mike:

I get &quot;application defined or object-defined error&quot; when I get to


do while i <= 1000
i = i + 1 THEN
if cells(i,0) = 0 THEN It stops here [/color blue]
bVal = cells(i, 2).value
cVal = cells(i, 3).value
exit do
end if
loop

After reading your explanation, this little proggie is exactly what I need. I have been struggling with this problem for weeks now.

 

[mwolf00]

argh!

if cells(i,0).value = 0 THEN Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[RobBroekhuis]

The problem isn't the missing .value (value is the default property of a range object anyway). It's that the column can't be zero. Without delving into all the code, I'd say perhaps you mean cells(i,1)?
Rob

 

[nathanstevenson]

Hi Mike,

Sorry to be a pain, it still brings up the same error. When I changed it to:

if cells(i,1).value = 0 THEN

it managed to loop through the lot, and it seemed to work a bit, but it skipped out the first occurence of the &quot;new val&quot; and seemed to put the ID on one line below.

It's pretty tricky dealing with these guys.
THanks so much for your help!
Nathan

 

[mwolf00]

Um, yeah....cells(i,1)

Thanks Rob.... Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[nathanstevenson]

Well I have tinkered with it, twirled it, whorled it, but alas it still does not work.

It is something to do with how the ID no is incremented, because it will skip some entries i.e where there should be an ID no = 5 it leaves it blank, and for some entries where the ID no should be the same, it is incremented.

Thanks for your help!

 

[RobBroekhuis]

Try the following code. I think it does what you need:

Sub AssignIDs()
Dim EndRow As Integer
EndRow = Range(&quot;B1&quot.End(xlDown).Row
Range(&quot;D:E&quot.Insert
Range(&quot;D1&quot;, Cells(EndRow, 4)).FormulaR1C1 = &quot;=CONCATENATE(RC[-2],&quot;&quot;#&quot;&quot;,RC[-1])&quot;
Range(&quot;E1&quot = 1
Range(&quot;E2&quot;, Cells(EndRow, 5)) = _
&quot;=IF(ISERROR(VLOOKUP(RC[-1],R1C4:R[-1]C[-1],1,FALSE)),MAX(R1C5:R[-1]C)+1,VLOOKUP(RC[-1],R1C4:R[-1]C,2,FALSE))&quot;
Range(&quot;E1&quot;, Cells(EndRow, 5)).Copy
Range(&quot;A1&quot.PasteSpecial xlPasteValues
Range(&quot;D:E&quot.Delete
End Sub

Rob

 

[nathanstevenson]

It worked!!!

Wow, thank you SO much. You have broken a 3 week long deadlock I have had that has all boiled down to this one little bit!

Brilliant!

 

[mwolf00]

The problem you experienced w/ my code is that I was looking at the wrong cell in the loop:

if i < 1000 If not, your done anyways
if cells(i+1,1) = 0 then See if the next cell has been identified as matching a higher row - if 0 then it has not
bVal = cells(i+1, 2).value
cVal = cells(i+1, 3).value
else find the next row where b and c have NOT been matched
do while i <= 1000
i = i + 1 THEN
if cells(i+1,1) = 0 THEN
bVal = cells(i+1, 2).value
cVal = cells(i+1, 3).value
exit do
end if
loop
end if
idNumber = IDnumber + 1 increment your id
end if


I'm glad that Rob's solution worked for you! Good job, Rob...
Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[nathanstevenson]

Brilliant, your code works like a dream too Mike!

Mike: If I had to compare more than two cols, would I just add in a dVal, eVal etc in the correct places in the code?

Rob: Are the extra columns, inserted in, and then deleted out?

Thanks so much both of you! You really have been extremely helpful!

Kind Regards,
Nathan

 

[mwolf00]

Yes, you can add as many columns as you need.... Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com