comparing columns

mynci · Feb 11th 2003

i have done quite a lot with excel in the past, mainly using vb scripts. but this one for some reason has me flumoxed.
i have two columns of data. could be numbers coulc be text (will only be one or the other though - ie either two columns of numbers or two columns of text.
i need to compare the data in the columns such that it tells me how many more occurances of a piece of data are in the first column compared to the first.
for example if column a had 1,2,2,2,3,4,4 and the second column had 2,2,3,4 i would want the spreadsheet to tell me that there were that there is 1 occurance of 1, 2 of 2 and 1 of 4 that is in the first column but not the first. if it could just output each instance in the first not in the second as a list, that would be fine as i can take it from there.

many thanks for your help. this one has really been bugging me.

mynci · Feb 11th 2003

cheers for youe help.
i had got to a similar sort of position before getting stuck.
is it posible to easily say: there are x occurances of y in column A. there x is the number of occurances and y is any number in column a? (bearing in mind there can be multiple occurances of any number in either column)
i could then apply the same to column b and compare the two outputs.
im sure this should be fairly simple, it just isn't obvious.
thanks a lot.

/edit/: ok im confused now - there was a reply to this when i wrote this reply- im not just talking to myself!

Anonymous · Feb 11th 2003

sorry, that was me....

the reply I posted only looked at column A and forgot about column B, so I deleted it...

figuring it wasn't easy via formulae, I was tinkering around with an attempted VBA solution (seeing as you seemed ok with VBA).... right now, I'm having trouble putting the info into arrays and then working on them......

there's some very good VBA guys look at this forum, I'll carry on with my clunky version attempt in the hope one of them turns up and saves the day

Anonymous · Feb 11th 2003

let's say your two columns are in column A and B..... green and pink in the example below

if you can insert some blank columns so you have them free up to column N

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=15 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - mynci.xls</TD><TD ALIGN=RIGHT>___Running: xl97 : OS = Windows (32-bit) 4.90</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=15><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb3414265196'><INPUT TYPE='Button' NAME='btCb0131056580' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb5317475802.sltNb1643179507.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=15><TABLE BORDER=0><TR><Form name='formFb5317475802'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb1643179507' onChange='document.formFb5317475802.txbFb4442305190.value = document.formFb5317475802.sltNb1643179507.value'><option value='=M2+N2'>J2<option value='=MIN(COUNTIF($A$2:$A$1000,L2),1)'>M2<option value='=MIN(COUNTIF($B$2:$B$1000,L2),1)'>N2<option value='=M3+N3'>J3<option value='=MIN(COUNTIF($A$2:$A$1000,L3),1)'>M3<option value='=MIN(COUNTIF($B$2:$B$1000,L3),1)'>N3<option value='=M4+N4'>J4<option value='=MIN(COUNTIF($A$2:$A$1000,L4),1)'>M4<option value='=MIN(COUNTIF($B$2:$B$1000,L4),1)'>N4<option value='=M5+N5'>J5<option value='=MIN(COUNTIF($A$2:$A$1000,L5),1)'>M5<option value='=MIN(COUNTIF($B$2:$B$1000,L5),1)'>N5<option value='=M6+N6'>J6<option value='=MIN(COUNTIF($A$2:$A$1000,L6),1)'>M6<option value='=MIN(COUNTIF($B$2:$B$1000,L6),1)'>N6<option value='=M7+N7'>J7<option value='=MIN(COUNTIF($A$2:$A$1000,L7),1)'>M7<option value='=MIN(COUNTIF($B$2:$B$1000,L7),1)'>N7<option value='=M8+N8'>J8<option value='=MIN(COUNTIF($A$2:$A$1000,L8),1)'>M8<option value='=MIN(COUNTIF($B$2:$B$1000,L8),1)'>N8<option value='=M9+N9'>J9<option value='=MIN(COUNTIF($A$2:$A$1000,L9),1)'>M9<option value='=MIN(COUNTIF($B$2:$B$1000,L9),1)'>N9<option value='=M10+N10'>J10<option value='=MIN(COUNTIF($A$2:$A$1000,L10),1)'>M10<option value='=MIN(COUNTIF($B$2:$B$1000,L10),1)'>N10<option value='=M11+N11'>J11<option value='=MIN(COUNTIF($A$2:$A$1000,L11),1)'>M11<option value='=MIN(COUNTIF($B$2:$B$1000,L11),1)'>N11<option value='=M12+N12'>J12<option value='=MIN(COUNTIF($A$2:$A$1000,L12),1)'>M12<option value='=MIN(COUNTIF($B$2:$B$1000,L12),1)'>N12<option value='=M13+N13'>J13<option value='=MIN(COUNTIF($A$2:$A$1000,L13),1)'>M13<option value='=MIN(COUNTIF($B$2:$B$1000,L13),1)'>N13<option value='=M14+N14'>J14<option value='=MIN(COUNTIF($A$2:$A$1000,L14),1)'>M14<option value='=MIN(COUNTIF($B$2:$B$1000,L14),1)'>N14<option value='=M15+N15'>J15<option value='=MIN(COUNTIF($A$2:$A$1000,L15),1)'>M15<option value='=MIN(COUNTIF($B$2:$B$1000,L15),1)'>N15</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb4442305190' size='120' value='=M2+N2'></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>F</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>G</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>H</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>I</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>J</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>K</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>L</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>M</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>N</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >How many</TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >Occurance(s)</TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >A</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >B</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >26</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M2+N2')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L2),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L2),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >31</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >92</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M3+N3')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L3),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L3),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >56</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >45</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >23</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M4+N4')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >23</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L4),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L4),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >23</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >76</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M5+N5')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L5),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L5),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >74</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >99</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >26</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M6+N6')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >26</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L6),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L6),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >31</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M7+N7')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >31</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L7),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L7),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >45</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M8+N8')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >45</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L8),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L8),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >56</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M9+N9')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >56</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L9),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L9),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >90</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M10+N10')>2</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L10),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L10),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >24</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >60</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M11+N11')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >74</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L11),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L11),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >74</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M12+N12')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >76</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L12),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L12),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >76</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M13+N13')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >90</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L13),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L13),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>14</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >90</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M14+N14')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >92</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L14),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L14),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>15</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >92</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M15+N15')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >99</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L15),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L15),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>16</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >99</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=15>Sheet1</TD></TR></TABLE> To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs. The above image was automatically generated by [HtmlMaker [email protected]]If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm>click here</A> to download This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]>Ivan F Moala</A> All credit to <A HREF=mailto:[email protected]>Colo</A> </CENTER>

the following VBA should work if you also add the column headings in H, J,K,L,M,N as per the example, just inserted as it's own module.... in my example it looks down 1000 rows... just adjust to suit if you see any time delays :

<PRE>

Sub mynci()

'clear old info

Range("H2:N2000").ClearContents

'advanced filter unique values of both columns to column H, filter again to column N and sort

Range("A2:A1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"H2"), Unique:=True
Range("B2:B1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H2").End(xlDown).Offset(1, 0), Unique:=True
Range("H2").Select
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("H2:H2000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"L2"), Unique:=True

'countif column A, minimising to 0 or 1

Range("M2").Formula = "=MIN(COUNTIF($A$2:$A$1000,L2),1)"
Range("M2").Copy Range(Range("L3"), Range("L3").End(xlDown)).Offset(0, 1)

'countif column B, minimising to 0 or 1

Range("N2").Formula = "=MIN(COUNTIF($B$2:$B$1000,L2),1)"
Range("N2").Copy Range(Range("L3"), Range("L3").End(xlDown)).Offset(0, 2)

'put in counts

Range("J2").Formula = "=M2+N2"
Range("J2").Copy Range(Range("L3"), Range("L3").End(xlDown)).Offset(0, -2)

'put in "occurance(s) of"

Range("K2").Value = "occurance(s) of"
Range("K2").Copy Range(Range("L3"), Range("L3").End(xlDown)).Offset(0, -1)

End Sub

</PRE>

where the yellow area is a listing of the results you wanted.

9/10 for clunkiness.... I'm sure it's 1/3 the size if VBA's arrays are utilised properly, alas, I don't quite have those skills yet

:tongue:

Anonymous · Feb 11th 2003

a similar example, using the same VBA code :

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=15 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - mynci.xls</TD><TD ALIGN=RIGHT>___Running: xl97 : OS = Windows (32-bit) 4.90</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=15><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb4287262003'><INPUT TYPE='Button' NAME='btCb5793506491' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb7224230428.sltNb9631429103.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=15><TABLE BORDER=0><TR><Form name='formFb7224230428'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb9631429103' onChange='document.formFb7224230428.txbFb5954886793.value = document.formFb7224230428.sltNb9631429103.value'><option value='=M2+N2'>J2<option value='=MIN(COUNTIF($A$2:$A$1000,L2),1)'>M2<option value='=MIN(COUNTIF($B$2:$B$1000,L2),1)'>N2<option value='=M3+N3'>J3<option value='=MIN(COUNTIF($A$2:$A$1000,L3),1)'>M3<option value='=MIN(COUNTIF($B$2:$B$1000,L3),1)'>N3<option value='=M4+N4'>J4<option value='=MIN(COUNTIF($A$2:$A$1000,L4),1)'>M4<option value='=MIN(COUNTIF($B$2:$B$1000,L4),1)'>N4<option value='=M5+N5'>J5<option value='=MIN(COUNTIF($A$2:$A$1000,L5),1)'>M5<option value='=MIN(COUNTIF($B$2:$B$1000,L5),1)'>N5<option value='=M6+N6'>J6<option value='=MIN(COUNTIF($A$2:$A$1000,L6),1)'>M6<option value='=MIN(COUNTIF($B$2:$B$1000,L6),1)'>N6<option value='=M7+N7'>J7<option value='=MIN(COUNTIF($A$2:$A$1000,L7),1)'>M7<option value='=MIN(COUNTIF($B$2:$B$1000,L7),1)'>N7<option value='=M8+N8'>J8<option value='=MIN(COUNTIF($A$2:$A$1000,L8),1)'>M8<option value='=MIN(COUNTIF($B$2:$B$1000,L8),1)'>N8<option value='=M9+N9'>J9<option value='=MIN(COUNTIF($A$2:$A$1000,L9),1)'>M9<option value='=MIN(COUNTIF($B$2:$B$1000,L9),1)'>N9<option value='=M10+N10'>J10<option value='=MIN(COUNTIF($A$2:$A$1000,L10),1)'>M10<option value='=MIN(COUNTIF($B$2:$B$1000,L10),1)'>N10<option value='=M11+N11'>J11<option value='=MIN(COUNTIF($A$2:$A$1000,L11),1)'>M11<option value='=MIN(COUNTIF($B$2:$B$1000,L11),1)'>N11</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb5954886793' size='120' value='=M2+N2'></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>F</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>G</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>H</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>I</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>J</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>K</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>L</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>M</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>N</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >How many</TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >Occurance(s)</TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >Amount</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >A</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >B</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M2+N2')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L2),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L2),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M3+N3')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L3),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L3),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M4+N4')>2</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L4),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L4),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M5+N5')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L5),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L5),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M6+N6')>2</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L6),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L6),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M7+N7')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L7),1)')>1</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L7),1)')>0</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M8+N8')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L8),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L8),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M9+N9')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L9),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L9),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M10+N10')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L10),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L10),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#CCFFCC ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FF99CC ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=M11+N11')>1</A></TD><TD BGCOLOR=#FFFF99 ALIGN=Left VALIGN=BOTTOM >occurance(s) of</TD><TD BGCOLOR=#FFFF99 ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($A$2:$A$1000,L11),1)')>0</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=MIN(COUNTIF($B$2:$B$1000,L11),1)')>1</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>14</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=15>Sheet1</TD></TR></TABLE> To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs. The above image was automatically generated by [HtmlMaker [email protected]]If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm>click here</A> to download This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]>Ivan F Moala</A> All credit to <A HREF=mailto:[email protected]>Colo</A> </CENTER>

it's nearly there, just seems to duplicate the first occurance of the 1s... hmmm

mynci · Feb 11th 2003

thanks very much for your help, however i can't quite get this to give me the correct results.
just to clarify the first column would always have more of a certain value than the second.
even so, the result i would want from the second example of numbers, would be- 1, 1, 1, 1, 3, 5, 5, 5. ie there are 4 additional 1's in the first column compared to the second, one three that is not in the second column etc...
i certainly dont mean to appear ungrateful but im not certain of how to modify your example to make it do this. if you could give me an additional push i would very much apreciate it.

many thanks

mynci · Feb 12th 2003

just a little update for you, i have written the code below, that will output the number on instances in each column to two columns, its not pretty code but it works.
now i just need to compare the data out of the two rows. and see if it works with the real data rather than the made up stuff i have used thus far.

cheers for your help.

just put in a module:

Sub Compare()
'---------------------------------------------------------
Dim Col As Integer
Dim Row As Integer
Dim Current As Integer
Dim Last As Integer
Dim Count As Integer
Dim OutRow As Integer
Dim OutCol As Integer
'---------------------------------------------------------
Row = 1
Count = 1
Col = 1
OutRow = 1
OutCol = 4
'---------------------------------------------------------

While Col < 3 'runs through the two columns

Last = 1
Current = 1

While Last <> 0

If Row = 1 Then
Current = Worksheets("Sheet1").Cells(Row, Col).Value
Row = Row + 1

Else:
Last = Current
Current = Worksheets("Sheet1").Cells(Row, Col).Value

If Current = Last Then
Count = Count + 1
Row = Row + 1
Else:
Worksheets("Sheet1").Cells(OutRow, OutCol + 1).Value = Count
Worksheets("Sheet1").Cells(OutRow, OutCol).Value = Last
OutRow = OutRow + 1
Count = 0
End If

End If

Wend
Col = Col + 1
OutCol = OutCol + 2
OutRow = 1
Row = 1
Wend

End Sub 'Compare

Anonymous · Feb 12th 2003

just working on something now, so give me a few minutes and I'll brb with, hopefully, exactly what you need

nice code btw.... most of mine's via the recorder

Anonymous · Feb 12th 2003

how about something like this : ?

assuming you're starting with your 2 columns something along these lines :

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=6 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - MYNCI2.xls</TD><TD ALIGN=RIGHT>___Running: xl97 : OS = Windows (32-bit) 4.90</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb7552370787'><INPUT TYPE='Button' NAME='btCb5705426622' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb0487398093.sltNb8859926925.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=6><TABLE BORDER=0><TR><Form name='formFb0487398093'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb8859926925' onChange='document.formFb0487398093.txbFb1960511720.value = document.formFb0487398093.sltNb8859926925.value'><option value=100>A1</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb1960511720' size='120' value=Column A></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Column A</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Column B</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >0</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>14</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>15</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>16</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >0</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>17</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>18</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>19</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>20</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>21</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>22</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>23</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>24</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>25</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>26</CENTER></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6>Sheet1</TD></TR></TABLE> To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs. The above image was automatically generated by [HtmlMaker [email protected]]If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm>click here</A> to download This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]>Ivan F Moala</A> All credit to <A HREF=mailto:[email protected]>Colo</A> </CENTER>

the following code, just in a normal module :

<PRE>

Sub column_a_and_b_occurances()

'Chris Davison
'[email protected]

'clear old info
Range("D1:G65536").ClearContents

'advance filter unique values in column A
Range(Range("A1"), Range("A1").End(xlDown)).Select
Range(Range("A1"), Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"D1"), Unique:=True

'advance filter unique values in column B
Range(Range("B1"), Range("B1").End(xlDown)).Select
Range(Range("B1"), Range("B1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"F1"), Unique:=True

'sort column A results
Range("D2").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'sort column B results
Range("F2").Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'insert headers
Range("E1").Value = "Column A narrative"
Range("G1").Value = "Column B narrative"

'insert respective formulae
Range("E2").FormulaR1C1 = _
"=""in Column A, ""&RC[-1]&"" occurs ""&MAX(0,COUNTIF(R2C1:R2000C1,RC4)-COUNTIF(R2C2:R2000C2,RC4))&IF(MAX(0,COUNTIF(R2C1:R2000C1,RC4)-COUNTIF(R2C2:R2000C2,RC4))=1,"" time"","" times"")&"" more than in Column B"""
Range("G2").FormulaR1C1 = _
"=""in Column B, ""&RC[-1]&"" occurs ""&MAX(0,COUNTIF(R2C2:R2000C2,RC6)-COUNTIF(R2C1:R2000C1,RC6))&IF(MAX(0,COUNTIF(R2C2:R2000C2,RC6)-COUNTIF(R2C1:R2000C1,RC6))=1,"" time"","" times"")&"" more than in Column A"""

'copy respective formula down each range
Range("E2").Copy Range(Range("D2"), Range("D2").End(xlDown)).Offset(0, 1)
Range("G2").Copy Range(Range("F2"), Range("F2").End(xlDown)).Offset(0, 1)

'return to an appropriate point
Range("D1").Select

End Sub

</PRE>

should give you something along the lines of the following, without the need to insert any headers or anything..... just make sure you have those spare columns to the right of your data columns :

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=8 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT>Microsoft Excel - MYNCI2.xls</TD><TD ALIGN=RIGHT>___Running: xl97 : OS = Windows (32-bit) 4.90</TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=8><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb2339942116'><INPUT TYPE='Button' NAME='btCb9273296640' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb4473621504.sltNb5698059465.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=8><TABLE BORDER=0><TR><Form name='formFb4473621504'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb5698059465' onChange='document.formFb4473621504.txbFb5434202903.value = document.formFb4473621504.sltNb5698059465.value'><option value='="in Column A, "&D2&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D2)-COUNTIF($B$2:$B$2000,$D2))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D2)-COUNTIF($B$2:$B$2000,$D2))=1," time"," times")&" more than in Column B"'>E2<option value='="in Column B, "&F2&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F2)-COUNTIF($A$2:$A$2000,$F2))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F2)-COUNTIF($A$2:$A$2000,$F2))=1," time"," times")&" more than in Column A"'>G2<option value='="in Column A, "&D3&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D3)-COUNTIF($B$2:$B$2000,$D3))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D3)-COUNTIF($B$2:$B$2000,$D3))=1," time"," times")&" more than in Column B"'>E3<option value='="in Column B, "&F3&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F3)-COUNTIF($A$2:$A$2000,$F3))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F3)-COUNTIF($A$2:$A$2000,$F3))=1," time"," times")&" more than in Column A"'>G3<option value='="in Column A, "&D4&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D4)-COUNTIF($B$2:$B$2000,$D4))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D4)-COUNTIF($B$2:$B$2000,$D4))=1," time"," times")&" more than in Column B"'>E4<option value='="in Column B, "&F4&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F4)-COUNTIF($A$2:$A$2000,$F4))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F4)-COUNTIF($A$2:$A$2000,$F4))=1," time"," times")&" more than in Column A"'>G4<option value='="in Column A, "&D5&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D5)-COUNTIF($B$2:$B$2000,$D5))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D5)-COUNTIF($B$2:$B$2000,$D5))=1," time"," times")&" more than in Column B"'>E5<option value='="in Column B, "&F5&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F5)-COUNTIF($A$2:$A$2000,$F5))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F5)-COUNTIF($A$2:$A$2000,$F5))=1," time"," times")&" more than in Column A"'>G5<option value='="in Column A, "&D6&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D6)-COUNTIF($B$2:$B$2000,$D6))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D6)-COUNTIF($B$2:$B$2000,$D6))=1," time"," times")&" more than in Column B"'>E6<option value='="in Column B, "&F6&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F6)-COUNTIF($A$2:$A$2000,$F6))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F6)-COUNTIF($A$2:$A$2000,$F6))=1," time"," times")&" more than in Column A"'>G6<option value='="in Column A, "&D7&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D7)-COUNTIF($B$2:$B$2000,$D7))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D7)-COUNTIF($B$2:$B$2000,$D7))=1," time"," times")&" more than in Column B"'>E7<option value='="in Column B, "&F7&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F7)-COUNTIF($A$2:$A$2000,$F7))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F7)-COUNTIF($A$2:$A$2000,$F7))=1," time"," times")&" more than in Column A"'>G7<option value='="in Column A, "&D8&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D8)-COUNTIF($B$2:$B$2000,$D8))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D8)-COUNTIF($B$2:$B$2000,$D8))=1," time"," times")&" more than in Column B"'>E8<option value='="in Column B, "&F8&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F8)-COUNTIF($A$2:$A$2000,$F8))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F8)-COUNTIF($A$2:$A$2000,$F8))=1," time"," times")&" more than in Column A"'>G8<option value='="in Column A, "&D9&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D9)-COUNTIF($B$2:$B$2000,$D9))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D9)-COUNTIF($B$2:$B$2000,$D9))=1," time"," times")&" more than in Column B"'>E9<option value='="in Column B, "&F9&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F9)-COUNTIF($A$2:$A$2000,$F9))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F9)-COUNTIF($A$2:$A$2000,$F9))=1," time"," times")&" more than in Column A"'>G9<option value='="in Column A, "&D10&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D10)-COUNTIF($B$2:$B$2000,$D10))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D10)-COUNTIF($B$2:$B$2000,$D10))=1," time"," times")&" more than in Column B"'>E10<option value='="in Column B, "&F10&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F10)-COUNTIF($A$2:$A$2000,$F10))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F10)-COUNTIF($A$2:$A$2000,$F10))=1," time"," times")&" more than in Column A"'>G10<option value='="in Column A, "&D11&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D11)-COUNTIF($B$2:$B$2000,$D11))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D11)-COUNTIF($B$2:$B$2000,$D11))=1," time"," times")&" more than in Column B"'>E11<option value='="in Column B, "&F11&" occurs "&MAX(0,COUNTIF($B$2:$B$2000,$F11)-COUNTIF($A$2:$A$2000,$F11))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F11)-COUNTIF($A$2:$A$2000,$F11))=1," time"," times")&" more than in Column A"'>G11</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb5434202903' size='120' value='="in Column A, "&D2&" occurs "&MAX(0,COUNTIF($A$2:$A$2000,$D2)-COUNTIF($B$2:$B$2000,$D2))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D2)-COUNTIF($B$2:$B$2000,$D2))=1," time"," times")&" more than in Column B"'></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>F</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>G</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Column A</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Column B</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Column A</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Column A narrative</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >Column B</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM >Column B narrative</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >0</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D2&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D2)-COUNTIF($B$2:$B$2000,$D2))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D2)-COUNTIF($B$2:$B$2000,$D2))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 0 occurs 2 times more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F2&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F2)-COUNTIF($A$2:$A$2000,$F2))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F2)-COUNTIF($A$2:$A$2000,$F2))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 1 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D3&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D3)-COUNTIF($B$2:$B$2000,$D3))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D3)-COUNTIF($B$2:$B$2000,$D3))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 1 occurs 0 times more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F3&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F3)-COUNTIF($A$2:$A$2000,$F3))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F3)-COUNTIF($A$2:$A$2000,$F3))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 2 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D4&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D4)-COUNTIF($B$2:$B$2000,$D4))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D4)-COUNTIF($B$2:$B$2000,$D4))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 2 occurs 0 times more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F4&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F4)-COUNTIF($A$2:$A$2000,$F4))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F4)-COUNTIF($A$2:$A$2000,$F4))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 3 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D5&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D5)-COUNTIF($B$2:$B$2000,$D5))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D5)-COUNTIF($B$2:$B$2000,$D5))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 3 occurs 1 time more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F5&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F5)-COUNTIF($A$2:$A$2000,$F5))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F5)-COUNTIF($A$2:$A$2000,$F5))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 4 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >0</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D6&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D6)-COUNTIF($B$2:$B$2000,$D6))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D6)-COUNTIF($B$2:$B$2000,$D6))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 4 occurs 1 time more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F6&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F6)-COUNTIF($A$2:$A$2000,$F6))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F6)-COUNTIF($A$2:$A$2000,$F6))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 5 occurs 5 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D7&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D7)-COUNTIF($B$2:$B$2000,$D7))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D7)-COUNTIF($B$2:$B$2000,$D7))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 6 occurs 0 times more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F7&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F7)-COUNTIF($A$2:$A$2000,$F7))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F7)-COUNTIF($A$2:$A$2000,$F7))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 6 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D8&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D8)-COUNTIF($B$2:$B$2000,$D8))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D8)-COUNTIF($B$2:$B$2000,$D8))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 7 occurs 0 times more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F8&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F8)-COUNTIF($A$2:$A$2000,$F8))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F8)-COUNTIF($A$2:$A$2000,$F8))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 7 occurs 1 time more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D9&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D9)-COUNTIF($B$2:$B$2000,$D9))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D9)-COUNTIF($B$2:$B$2000,$D9))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 8 occurs 2 times more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F9&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F9)-COUNTIF($A$2:$A$2000,$F9))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F9)-COUNTIF($A$2:$A$2000,$F9))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 8 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D10&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D10)-COUNTIF($B$2:$B$2000,$D10))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D10)-COUNTIF($B$2:$B$2000,$D10))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 9 occurs 1 time more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F10&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F10)-COUNTIF($A$2:$A$2000,$F10))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F10)-COUNTIF($A$2:$A$2000,$F10))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 9 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20A,%20%22&D11&%22%20occurs%20%22&MAX(0,COUNTIF($A$2:$A$2000,$D11)-COUNTIF($B$2:$B$2000,$D11))&IF(MAX(0,COUNTIF($A$2:$A$2000,$D11)-COUNTIF($B$2:$B$2000,$D11))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20B%22')>in Column A, 100 occurs 4 times more than in Column B</A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=%22in%20Column%20B,%20%22&F11&%22%20occurs%20%22&MAX(0,COUNTIF($B$2:$B$2000,$F11)-COUNTIF($A$2:$A$2000,$F11))&IF(MAX(0,COUNTIF($B$2:$B$2000,$F11)-COUNTIF($A$2:$A$2000,$F11))=1,%22%20time%22,%22%20times%22)&%22%20more%20than%20in%20Column%20A%22')>in Column B, 100 occurs 0 times more than in Column A</A></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>14</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>15</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>16</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >0</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>17</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>18</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >100</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>19</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>20</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR&am

Denis · Feb 12th 2003

Chris,

Nice solution. I was trying to craft a solution using "AutoFilter", since I was experimenting with that for another post. I'd never used a Filter in code before.

@ CD :coolrsvd:

Anonymous · Feb 12th 2003

Hey, thanks Denis... although I must admit to tinkering with the macro recorder for help

I'm just looking at the questions again too and will provide a better solution tommorrow after bedtime today (11.30pm here now)....

I think we need to utilise a COUNT and then plug it back into a =REPT function.... so that it repeats the numbers x amount of times...

shouldn't be too difficult

when it comes to VBA, I am merely a beginner though, I'm much more at home with formulae and functions

hey, what's *this* smiley : :cul:

is he bald ?

:wow:

Denis · Feb 12th 2003

Quote

is he bald ?

If I asked some of the guys I work with they might think that it is an elbow.

mynci · Feb 13th 2003

i have finally sorted it.
this code run on the first two columns of the spreadsheet outputs the data to two columns with the name next to them and then a simple Ax-Bx style eqn finds the differences.
cheers for all your help.
its not pretty code, and takes an age to run on large data sets, but it works.
the columns need to be separatly sorted, alphabetically/numerically first.
just put it in a module.

Sub Compare()
'---------------------------------------------------------
Dim Col As Integer
Dim Row As Integer
Dim RowTwo As Integer
Dim Current As String
Dim Compare As String
Dim Last As String
Dim Count As Integer
Dim CountTwo As Integer
Dim OutRow As Integer
Dim OutCol As Integer
'---------------------------------------------------------
Row = 1
Count = 1
Col = 1
OutRow = 1
OutCol = 4
'---------------------------------------------------------

While Col < 2 'runs through the two columns

Last = 1
Current = 1

While Last <> ""

If Row = 1 Then
Current = Worksheets("Sheet1").Cells(Row, Col).Value
Row = Row + 1

Else:
Last = Current
Current = Worksheets("Sheet1").Cells(Row, Col).Value

If Current = Last Then
Count = Count + 1
Row = Row + 1
Else:
Worksheets("Sheet1").Cells(OutRow, OutCol + 1).Value = Count
Worksheets("Sheet1").Cells(OutRow, OutCol).Value = Last
OutRow = OutRow + 1
Count = 0
RowTwo = 1
CountTwo = 0

While Worksheets("Sheet1").Cells(RowTwo, Col + 1).Value <> ""
Compare = Worksheets("Sheet1").Cells(RowTwo, Col + 1).Value
If Last = Compare Then
CountTwo = CountTwo + 1
End If
RowTwo = RowTwo + 1
Wend
Worksheets("Sheet1").Cells(OutRow - 1, OutCol + 2).Value = CountTwo

End If

End If

Wend
Col = Col + 1
OutRow = 1
Row = 1
Wend

End Sub 'Compare

Anonymous · Feb 13th 2003

excellent work !

if it takes ages, try putting :

application.screenupdating = false

right at the beginning of the code, under the title

then switch it back on again with :

application.screenupdating = true

right at the end of the code, before your "end sub"

this will just switch off the need to actually update the screen..... the work is still done, just you won't see it whilst it happens : it should at least halve the time taken

:yes:

mynci · Feb 13th 2003

thanks thats a great tip. thanks for all your help.
i think it nearly took me long enough to write the code to do it by hand, but never mind it was a learning excersise, or something.
thanks again.

comparing columns

Participate now!

Share