Index match / vlookup

[skuddyb]

Hi,

So I'm pretty sure INDEX MATCH or a variation of will be able to help but I can't quite get it right.

- In column H I have a list of towns.
- In column Y I have a combination of 1's and 0's going down the spread sheet.
- I want to be able to record a town if there is a 1 in the corresponding cell in column Y.
- I want to be able to record how many of each town is recorded.


H I Y

Towns	Counties	Response
Birmingham	WM	1
Oxford	Oxfordshire	0
Kettering	Northamptonshire	1
Birmingham	WM	1
London	London	1
Manchester	GM	0
Liverpool	Merseyside	0
Milton Keynes	Buckinghamshire	1
Oxford	Oxfordshire	1

<tbody>
</tbody>

My Results would therefore be:

Birmingham 2
Kettering 1
London 1
MK 1
Oxford 1

 

[123rickfear]

would a countifs formula not work? such as =countifs(H:H,"Birmingham",Y:Y,"1")

 

[nabeelzz]

Filter unique values from 1st column.
To do that paste the first column (in ur case column "H") in a separate sheet -- select full column and press Alt + A + M. Press OK for the message shown.
This will give u unique values in column H. Then on those unique values you could apply countifs formula as suggested by 123rickfear above.

 

[Fishboy]

Hi,

So I'm pretty sure INDEX MATCH or a variation of will be able to help but I can't quite get it right.

- In column H I have a list of towns.
- In column Y I have a combination of 1's and 0's going down the spread sheet.
- I want to be able to record a town if there is a 1 in the corresponding cell in column Y.
- I want to be able to record how many of each town is recorded.


H I Y

Towns	Counties	Response
Birmingham	WM	1
Oxford	Oxfordshire	0
Kettering	Northamptonshire	1
Birmingham	WM	1
London	London	1
Manchester	GM	0
Liverpool	Merseyside	0
Milton Keynes	Buckinghamshire	1
Oxford	Oxfordshire	1

<tbody>
</tbody>

My Results would therefore be:

Birmingham 2
Kettering 1
London 1
MK 1
Oxford 1

Hi skuddyb,

Assuming that your results start in A2 (with a header already in A1) and the response count for your results is in B2 (with a header already in B1), then you could use the following VBA to achieve this:

Sub CountTownResponses()
' Defines variables
Dim Cell As Range, cRange As Range, sRange As Range, Rng As Range, FindString As String
    ' Defines last row of columns Y (the count list) and A (the output list)
    LastRowCheck = ActiveSheet.Cells(Rows.Count, "Y").End(xlUp).Row
    LastRowList = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
        ' Set the range to check as Y2 to the last row of Y
        Set cRange = Range("Y2:Y" & LastRowCheck)
            ' For each cell in the check range
            For Each Cell In cRange
                ' If the cell value is a 1 then...
                If Cell.Value = 1 Then
                    ' Set the FindString as the value from the corresponding H column
                    FindString = Range("H" & Cell.Row).Value
                        ' Set the search range as A1 to the last row of A
                        Set sRange = Range("A1:A" & LastRowList)
                            ' With the search range
                            With sRange
                                ' Set Rng as the cell where the value is found
                                Set Rng = .Find(What:=FindString, _
                                                After:=.Cells(1), _
                                                LookIn:=xlValues, _
                                                LookAt:=xlWhole, _
                                                SearchOrder:=xlByRows, _
                                                SearchDirection:=xlPrevious, _
                                                MatchCase:=False)
                                    ' If Rng does not exist then
                                    If Rng Is Nothing Then
                                        ' Update the output list with the cell value and count
                                        Range("A" & LastRowList).Value = Range("H" & Cell.Row).Value
                                            Range("B" & LastRowList).Value = Range("Y" & Cell.Row).Value
                                                ' Increase LastRowList by 1 to account for the new data
                                                LastRowList = LastRowList + 1
                                    ' Else if Rng already exists in the output list then...
                                    Else
                                        ' Update the corresponding response count in the output list by 1
                                        Rng.Offset(0, 1).Value = Rng.Offset(0, 1).Value + 1
                                    End If
                            End With
                End If
            ' Check next cell in the check range
            Next Cell
                    
End Sub

 

[skuddyb]

Cheers for this guys, may definitely hep me in future.

Managed to simply use Pivot Tables to show geographic response by town and county, and it presents the data really easily to read.

Thanks again.

 

[W8253]

Create a pivottable with:

Rows: Towns
Values: Sum of Response (Change from Count to Sum)