Loop through column values from one sheet and paste COUNTIF value from another column into another sheet

0
votes

I have two sheets in an Excel file and need to perform a COUNTIF formula from one sheet and paste the respective information in another sheet. The original sheet just has the type in 1st column with an empty 2nd column. I am trying to loop through the Type from Sheet 1, in each increment loop through the Type from Sheet 2, and past the Count of column 2 from Sheet 2 into Column 2 of sheet 1.

My current VBA code is as follows:

Sub TestOE()
    'For loop to go until end of filled cells in 1st column of each sheet
    a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

    'Loop
    For i = 2 To a
        For j = 2 To b
            If Worksheets("Sheet1").Cells(i, 1).Value = Worksheets("Sheet2").Cells(j, 1).Value Then
                Worksheets("Sheet1").Cells(i, 2).Value = Application.WorksheetFunction.CountIf(Range("B:B"), 1)
            End If
        Next j
    Next i
End Sub

This code is only pasting 0's in the desired outcome on Sheet 1.

Sheet to extract information from
Sheet to extract information from

Sheet to paste information in
Main Sheet to paste information in

Desired Outcome in destination sheet
Desired Outcome in main sheet

2
vbaexcel
I also had a previous version of the code that would produce the same COUNTIF outcome for all types, because it would COUNT all of the binary values. However, I only want it to count the values for that specific type. – Ryan
Why not use a pivot table? – Scott Craner
@Ryan If one of the solutions satisfy your need, please accept the answers.. if all the answers doesn't work for you, Kindly drop a comment to share what difficulties you'd faced... – p._phidot_

2 Answers

0
votes

You can simply use sumif function to sum the values based on criteria.

here is the formula

=COUNTIF(Sheet1!$A$2:$A$20,Sheet2!A2)

if you want to sum the col B then

=SUMIF(Sheet1!$A$2:$A$20,Sheet2!A2,Sheet1!$B$2:$B$20)

0
votes

In a few steps, you can accomplish what you want without VBA, and just use a pivot table. Just do as follows.

  1. Select your data set, including the header.
  2. Click on insert tab, then PivotTable. See example for Office 365
  3. Since you want a different worksheet, set PivotTable to be "New Worksheet" See example.
  4. You'll need to drag the TYPE field into the rows, and binary into the values. CountIF is the same as summing binary, so you can leave as sum. See Example

And you'll have an output nearly identical to what you're looking for: Final Result