Matches in multiple columns

[canabill]

The VBA code below matches numbers (indicating 0 or 1) between column A and column B and lists them in single column D. How would I list them in multiple columns (preferably 4) instead?


Public Sub CountA_FillC()

Dim RowA As Long, RowB As Long
Dim UsedRange As Range: Set UsedRange = ActiveSheet.UsedRange
For RowB = 1 To UsedRange.Rows.Count
Dim Count As Long: Count = 0
For RowA = 1 To UsedRange.Rows.Count
If UsedRange(RowA, "A").Value = UsedRange(RowB, "B").Value Then
Count = Count + 1
End If
Next RowA
UsedRange(RowB, "D").Value = Count
Next RowB

End Sub

 

[canabill]

Code in [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4 [/URL] does what you need, based on the test sheet I created...

Initially numbers were in cols A & B and your code is using Count = Count + 1, for me it generated values > 1 which outputs into column D

Your ask was to change this single column output, to 4 columns, L:R

Now you're stating you have data in columns K:Q; code in #4 would overwrite this.

You may want to start again with a clean sheet for each piece of code suggested, run and test and then confirm if any code works for you or if none do why - given you're struggling to upload file.

Finally my test data was in cells A1:A40 =ROW() then I copied and pasted these values into A1:B40 and again into A41:B80, so each row would have matching pairs and each pair would exist at least twice in A1:B80

Thanks again. I have tested all codes but will try it again and I'll send my Excel file using Dropbox if needed, #4 works correctly except it only fills in numbers 1 to 20 in column L only and leaves the rest blank. Just to clarify column A has 20 numbers and B has 1 to 80 listed straight down from B1.

 

[canabill]

I installed Dropbox as suggested so I'll send you both files. Please let me know where to send them.

 

[Rick Rothstein]

I installed Dropbox as suggested so I'll send you both files. Please let me know where to send them.

You don't "send them" anywhere. After you put a copy of your file into your DropBox folder on your computer, I think (it has been a while since I did this) that you right-click the file inside of your drop box folder and select "Share" from the popup menu... on the dialog box that appears, click the "Create link" hyperlink and then click the "Copy link" hyperlink that appears (this will copy the link into your Clipboard), then paste that link into a reply here on this forum... we will then be able to access your file via that link.

 

[canabill]

Thanks to both of you, I'll mention one more thing then maybe I won't need to use Dropbox.
Rick your code works but I notice now that the one & zero matches are listed in L,N,P & R in rows rather than columns making it appear that they're incorrect. It would be perfect if they could be listed in columns, the matches 1 to 20 in L1 to L20, 21 to 40 in N1 to N20 and so on.