The difference of two rows of numbers

[sphinxwt]

Hi all,

I have a set of code to generate a new column of numbers that are present in column B but not in column A. But now I have some data stored in rows, and I have troubles to modify the code to generate a new row to show the difference of two rows of numbers. can someone pls advise. Thx a lot!

here is the original code for columns

Sub Set_Difference()
Dim colA, colB, ArrList()
Dim i As Integer, j As Integer
Dim theList As New Collection
Dim IsExist As Boolean

Range("F2:F65536").ClearContents

On Error Resume Next
colA = Range("A2:" & "A" & [A65536].End(xlUp).Row)
colB = Range("B2:" & "B" & [B65536].End(xlUp).Row)
For i = 1 To [B65536].End(xlUp).Row - 1
  For j = 1 To [A65536].End(xlUp).Row - 1
   If colB(i, 1) = colA(j, 1) Then
     IsExist = True
     Exit For
   End If
  Next
  If IsExist Then
    IsExist = False
  Else
    theList.Add colB(i, 1), CStr(colB(i, 1))
  End If
Next

ReDim ArrList(1 To theList.Count, 1 To 1)
For i = 1 To theList.Count
  ArrList(i, 1) = theList(i)
Next
Range("F2:" & "F" & theList.Count + 1).Value = ArrList

End Sub

 

[sphinxwt]

Hi, it works perfectly now!!!
Thanks A LOT!!!

 

[mirabeau]

Hi, it works perfectly now!!!
Thanks A LOT!!!

That's good to know.
Thanks for the feedback.

 

[sphinxwt]

That's good to know.
Thanks for the feedback.

Hi mirabeau, I find there is range problem. The macro could not find all the difference items between the two rows..
e.g. a row in sheet 1 has empty record, but the same row in sheet 2 has 134. So all the 134 records should be counted as difference. However the macro could only generate the first 102 of them in sheet 3. I couldn't determine which part of the code renders this bug.
the data type is integer.

 

[mirabeau]

Hi mirabeau, I find there is range problem. The macro could not find all the difference items between the two rows..
e.g. a row in sheet 1 has empty record, but the same row in sheet 2 has 134. So all the 134 records should be counted as difference. However the macro could only generate the first 102 of them in sheet 3. I couldn't determine which part of the code renders this bug.
the data type is integer.

Could you post a sample few rows of data where you think the code goes wrong?

Like, 4 or 5 rows (with enough columns to read easily) from sheet1, 4 or 5 rows from sheet2, and a couple of rows showing what the code produces and also what the result should look like?

 

[sphinxwt]

I tested and found that the error happens when there are rows in sheet 2 exceeds the 256 columns limit.
Is there any solution on this?

 

[sphinxwt]

Could you post a sample few rows of data where you think the code goes wrong?

Like, 4 or 5 rows (with enough columns to read easily) from sheet1, 4 or 5 rows from sheet2, and a couple of rows showing what the code produces and also what the result should look like?

ufff....I wish i could send you part of my data somehow... for example, some rows in sheet 1 have 2-3 columns, but 250-350 in sheet 2. The result should be almost the same length as in sheet2, but some of them (e.g. the last 50 biggest numbers) could not be generated in sheet3.

However, I made up another file which has the similar row length but with random-generated numbers (sorted). With this made-up data and the same code, the result would somehow became right. It doesn't make any sense...

 

[sphinxwt]

Here is two rows from my original data. Running the code with nrow = 2, the row1 can not generate all the difference numbers in sheet3

"sheet1"

row1 211 225
row2 207 211 225

"sheet2"

row1 1 2 3 5 8 10 12 13 14 15 16 17 19 20 21 22 23 24 25 26 29 30 31 35 36 37 38 44 49 51 54 59 60 64 65 66 67 68 69 70 72 76 77 78 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 102 103 104 105 106 107 108 109 110 111 112 120 123 127 132 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 155 157 158 159 160 161 162 163 164 166 167 168 169 171 172 173 177 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 205 207 208 212 221 225 228 230 231 236 243 244 246 247 250 251 255 256 258 259 260 261 262 263 264 265 266 267 268 269 271 272 274 275 276 279 280 281 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 324 327 329 330 331 332 334 335 336 339 340 341 342 343 345 347 348 349 350 351 352 353 354 355 356 357 360 362 363 365 366 367 370 371 372 373 374 375 376 377 379 380 382 383 384 386 387 388 389 390 391 392 393 394 395 396 398 400 401 403 404 405 406 407 408 409 411 412 413 414 415 416 418 419 420 422 425 426 427 428 429 430 431 432 433 434 435 436 437 439 440 441 442 443 444 445 446 447 448 449 450 451 453 454 455 456 457 458 461 463 464 465 466 467 468 473 476 477 479 480 483 485 486 487 489 490 494 497
row2 1 2 3 5 8 10 12 13 14 15 16 17 19 20 21 22 23 24 25 26 29 30 31 35 36 37 38 44 51 54 59 65 66 67 68 69 70 72 76 77 78 80 81 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 102 104 105 106 107 108 109 110 111 112 120 123 127 132 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 155 157 158 159 160 162 163 164 166 167 168 169 171 172 173 184 185 186 187 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 205 207 208 212 225 228 230 231 236 243 244 246 247 250 251 255 256 258 259 260 262 263 264 265 266 267 268 269 271 272 274 275 276 279 280 281 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 324 327 329 330 331 332 334 335 336 339 340 341 342 343 345 347 348 349 350 351 352 353 354 355 356 357 360 362 363 365 366 367 371 372 373 374 375 376 377 379 380 382 383 384 386 387 388 389 390 391 392 393 394 395 396 398 400 401 403 405 406 407 409 411 412 413 414 415 416 418 419 420 422 425 426 427 428 429 430 431 432 433 434 435 436 437 439 440 441 442 443 444 445 446 447 448 449 450 451 453 454 455 456 458 461 463 464 465 466 467 468 473 476 479 480 483 485 486 487 489 490 494 497

 

[mirabeau]

The problem arising from those rows was due to an oversight by me in that part of the code determining the last used columns.

I overlooked changing two entries.

Try the following code, changes in red from the earlier version.

Sub diffs() '16 July 11
Dim dic As Object
Dim nc1 As Long, nc2 As Long
Dim rng1, rng2
Dim nrow As Long
Dim c()
Dim i As Long, j As Long, k As Long
Dim u As Long

nrow = 8000
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = 1
With Sheets("sheet1").Cells
    nc1 = .Find("*", after:=.Cells(1), searchorder:=xlByColumns, _
        searchdirection:=xlPrevious).Column
    rng1 = .Cells(1).Resize(nrow, nc1)
End With
With Sheets("sheet2").Cells
    nc2 = .Find("*", after:=.Cells(1), searchorder:=xlByColumns, _
        searchdirection:=xlPrevious).Column
    rng2 = .Cells(1).Resize(nrow, nc2)
End With
ReDim c(1 To nrow, 1 To Application.Max(nc1, nc2))

For i = 1 To nrow
For j = 1 To nc1
    If rng1(i, j) <> Empty Then dic(rng1(i, j)) = 1
Next j
For j = 1 To nc2
    If rng2(i, j) <> Empty And Not dic.exists(rng2(i, j)) Then
        k = k + 1
        If k > 0 Then c(i, k) = rng2(i, j)
    End If
Next j
If k > u Then u = k
k = 0
dic.RemoveAll
Next i
Sheets("sheet3").Range("A1").Resize(nrow, u) = c
End Sub

 

[sphinxwt]

Hi, now it works just perfect and I understand how the code works.
Can't thank you enough..

 

[mirabeau]

Great.

Thanks for the feedback.