| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way for comparing columns?
Hi Group,
I am using Chip Pearon's excellent formula for comparing values in various cols, i.e Col B & Col A and vice versa. It works well but it has become very, very, very slow due to the size of the ranges I'm working with (10,000+) rows. Is there a faster method available (VBA?) to allow me to produce similiar results ? Here is Chip's formula. =IF(INDIRECT(ADDRESS(ROW(),COLUMN(Range2)))="","", IF (COUNTIF(Range1,INDIRECT(ADDRESS(ROW(),COLUMN(Rang e2),4))) =0,INDIRECT(ADDRESS(ROW(),COLUMN(Range2),4)),"")) Ideally I would like to produce the results... if in Col A but not in Col B put in Col C if in Col B but not in Col A put in Col D If duplicates put in Col E I amusing Excel 2000 on pentium 4. Regards Tony |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fastest way for comparing columns?
Hi,
I assume that the two columns are numbers - well my example does anyway: Other things to note - you can test for the last row, and place that into the code instead of it being hard coded in this example. I used randbetween for column A and B to generate numbers between 1 and 100 for 10,000 rows to see how long it took - answers were populated within 8 seconds. I am sure the code could be improved....... Option Explicit Sub try_this() Dim intrwindex As Integer Dim intcolindex As Integer Dim int_cola As Integer Dim int_colb As Integer intcolindex = 1 For intrwindex = 1 To 10000 int_cola = Cells(intrwindex, intcolindex).Value int_colb = Cells(intrwindex, intcolindex).Offset(0, 1).Value If str_cola < str_colb Then Cells(intrwindex, intcolindex).Offset(0, 2).Value = int_cola Cells(intrwindex, intcolindex).Offset(0, 3).Value = int_colb Else Cells(intrwindex, intcolindex).Offset(0, 4).Value = int_cola End If Next intrwindex End Sub HTH -----Original Message----- Hi Group, I am using Chip Pearon's excellent formula for comparing values in various cols, i.e Col B & Col A and vice versa. It works well but it has become very, very, very slow due to the size of the ranges I'm working with (10,000+) rows. Is there a faster method available (VBA?) to allow me to produce similiar results ? Here is Chip's formula. =IF(INDIRECT(ADDRESS(ROW(),COLUMN(Range2)))="","" ,IF (COUNTIF(Range1,INDIRECT(ADDRESS(ROW(),COLUMN(Ran ge2),4))) =0,INDIRECT(ADDRESS(ROW(),COLUMN(Range2),4)),"" )) Ideally I would like to produce the results... if in Col A but not in Col B put in Col C if in Col B but not in Col A put in Col D If duplicates put in Col E I amusing Excel 2000 on pentium 4. Regards Tony . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fastest way for comparing columns?
Wow, that IS fast Alan. Many thanks indeed
Mike - will look at array formulas as suggested. T. -----Original Message----- Hi, I assume that the two columns are numbers - well my example does anyway: Other things to note - you can test for the last row, and place that into the code instead of it being hard coded in this example. I used randbetween for column A and B to generate numbers between 1 and 100 for 10,000 rows to see how long it took - answers were populated within 8 seconds. I am sure the code could be improved....... Option Explicit Sub try_this() Dim intrwindex As Integer Dim intcolindex As Integer Dim int_cola As Integer Dim int_colb As Integer intcolindex = 1 For intrwindex = 1 To 10000 int_cola = Cells(intrwindex, intcolindex).Value int_colb = Cells(intrwindex, intcolindex).Offset(0, 1).Value If str_cola < str_colb Then Cells(intrwindex, intcolindex).Offset(0, 2).Value = int_cola Cells(intrwindex, intcolindex).Offset(0, 3).Value = int_colb Else Cells(intrwindex, intcolindex).Offset(0, 4).Value = int_cola End If Next intrwindex End Sub HTH -----Original Message----- Hi Group, I am using Chip Pearon's excellent formula for comparing values in various cols, i.e Col B & Col A and vice versa. It works well but it has become very, very, very slow due to the size of the ranges I'm working with (10,000+) rows. Is there a faster method available (VBA?) to allow me to produce similiar results ? Here is Chip's formula. =IF(INDIRECT(ADDRESS(ROW(),COLUMN(Range2)))=""," ",IF (COUNTIF(Range1,INDIRECT(ADDRESS(ROW(),COLUMN (Range2),4))) =0,INDIRECT(ADDRESS(ROW(),COLUMN(Range2),4)),"") ) Ideally I would like to produce the results... if in Col A but not in Col B put in Col C if in Col B but not in Col A put in Col D If duplicates put in Col E I amusing Excel 2000 on pentium 4. Regards Tony . . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fastest way for comparing columns?
Matching cells are always side by side?
-- Regards, Tom Ogilvy "Tony" wrote in message ... Wow, that IS fast Alan. Many thanks indeed Mike - will look at array formulas as suggested. T. -----Original Message----- Hi, I assume that the two columns are numbers - well my example does anyway: Other things to note - you can test for the last row, and place that into the code instead of it being hard coded in this example. I used randbetween for column A and B to generate numbers between 1 and 100 for 10,000 rows to see how long it took - answers were populated within 8 seconds. I am sure the code could be improved....... Option Explicit Sub try_this() Dim intrwindex As Integer Dim intcolindex As Integer Dim int_cola As Integer Dim int_colb As Integer intcolindex = 1 For intrwindex = 1 To 10000 int_cola = Cells(intrwindex, intcolindex).Value int_colb = Cells(intrwindex, intcolindex).Offset(0, 1).Value If str_cola < str_colb Then Cells(intrwindex, intcolindex).Offset(0, 2).Value = int_cola Cells(intrwindex, intcolindex).Offset(0, 3).Value = int_colb Else Cells(intrwindex, intcolindex).Offset(0, 4).Value = int_cola End If Next intrwindex End Sub HTH -----Original Message----- Hi Group, I am using Chip Pearon's excellent formula for comparing values in various cols, i.e Col B & Col A and vice versa. It works well but it has become very, very, very slow due to the size of the ranges I'm working with (10,000+) rows. Is there a faster method available (VBA?) to allow me to produce similiar results ? Here is Chip's formula. =IF(INDIRECT(ADDRESS(ROW(),COLUMN(Range2)))=""," ",IF (COUNTIF(Range1,INDIRECT(ADDRESS(ROW(),COLUMN (Range2),4))) =0,INDIRECT(ADDRESS(ROW(),COLUMN(Range2),4)),"") ) Ideally I would like to produce the results... if in Col A but not in Col B put in Col C if in Col B but not in Col A put in Col D If duplicates put in Col E I amusing Excel 2000 on pentium 4. Regards Tony . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fastest way for comparing columns?
Typically an Array Formula is the fastest way to compare 2 lists. Coupled
with an IF statement in column C, compare A to B, if result =0 then = the value in A, if not then "". In column D do the same to compare B to A. Now an IF statement in column E to to see if the cell in column C or D is "". If C is "" then the value from A, If D is "" then value from B, If neither is "", then "". Chip's website can help with the array formula. Mike F "Tony" wrote in message ... Hi Group, I am using Chip Pearon's excellent formula for comparing values in various cols, i.e Col B & Col A and vice versa. It works well but it has become very, very, very slow due to the size of the ranges I'm working with (10,000+) rows. Is there a faster method available (VBA?) to allow me to produce similiar results ? Here is Chip's formula. =IF(INDIRECT(ADDRESS(ROW(),COLUMN(Range2)))="","", IF (COUNTIF(Range1,INDIRECT(ADDRESS(ROW(),COLUMN(Rang e2),4))) =0,INDIRECT(ADDRESS(ROW(),COLUMN(Range2),4)),"")) Ideally I would like to produce the results... if in Col A but not in Col B put in Col C if in Col B but not in Col A put in Col D If duplicates put in Col E I amusing Excel 2000 on pentium 4. Regards Tony |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing two columns | Excel Worksheet Functions | |||
| Comparing Columns to each other | Excel Discussion (Misc queries) | |||
| Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
| comparing columns | Excel Discussion (Misc queries) | |||
| Fastest way to do this? | Excel Programming | |||
Hybrid Mode
