# Index match / vlookup

#### skuddyb

##### New Member
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
would a countifs formula not work? such as =countifs(H:H,"Birmingham",Y:Y,"1")

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.

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:

Rich (BB code):
``````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``````

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.

Create a pivottable with:

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

Replies
5
Views
457
Replies
10
Views
485
Replies
2
Views
551
Replies
1
Views
585
Replies
1
Views
438

1,218,811
Messages
6,144,613
Members
450,560
Latest member
afcmRamos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back