Index match / vlookup

skuddyb

New Member
Joined
Sep 29, 2015
Messages
39
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
TownsCountiesResponse
BirminghamWM1
OxfordOxfordshire0
KetteringNorthamptonshire1
BirminghamWM1
LondonLondon1
ManchesterGM0
LiverpoolMerseyside0
Milton Keynes Buckinghamshire1
OxfordOxfordshire1

<tbody>
</tbody>


My Results would therefore be:

Birmingham 2
Kettering 1
London 1
MK 1
Oxford 1
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

nabeelzz

Board Regular
Joined
Sep 23, 2014
Messages
69
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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
TownsCountiesResponse
BirminghamWM1
OxfordOxfordshire0
KetteringNorthamptonshire1
BirminghamWM1
LondonLondon1
ManchesterGM0
LiverpoolMerseyside0
Milton KeynesBuckinghamshire1
OxfordOxfordshire1

<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
 

skuddyb

New Member
Joined
Sep 29, 2015
Messages
39
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

Board Regular
Joined
Oct 7, 2015
Messages
64
Create a pivottable with:

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

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top