Solved: =IF Formula Help

[jmm007]

Right now im doing some chart work that has me listing a location of where drivers are going on a day to day basis and when the end of the month or year comes i need to know locations they have been and how many times. so say my sheet is placed out where the location name is listed at F3 and countinueing down so F4 f5 f6 f7 f8 and so on infinite until the end of the year.

How do i make an =IF equatiin or even a macro that can sort out those locations for me

say it was set up like this



Location
a
a
a
b
b
c
c
a
c
a
d
a
a
a

Now i need it to show me how many times they went to certain locations on another sheet. is this possible?

so on Other sheet it would look like

Location A = 7
Location B = 2
Location C = 3
Location D = 1

But the formula would have to allow for the addition of many many locations to look for. cause we have over 100+ locations, some more times traveled then others.

what can be done?

[Aussiebear]

Please post your workbook. Click on Go Advanced|Manage attachments and upload the workbook.

[jmm007]

I think i did it...check now

[jmm007]

BTW Names have been taken out due to sensitivity if that hinders your progress i apologize, also numbers have been changed as well , that shouldn't effect you any though

[mbarron]

The following uses:
Advance Filter (Copy Unique Values to another location options)
Copy/Paste Special / Values

[VBA]Sub UniqueCountifMove()
Application.ScreenUpdating = False
Dim rngF As Range, lRow1 As Long, lCol As Integer, lRow2 As Long
lRow1 = Range("F2").End(xlDown).Row
Set rngF = Range("F2:F" & lRow1)
lCol = Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).Column
rngF.AdvancedFilter Action:=xlFilterCopy, copytorange:=Cells(2, lCol), _
unique:=True

Cells(2, lCol + 1) = "Count"
lRow2 = Cells(2, lCol).End(xlDown).Row
Range(Cells(3, lCol + 1), Cells(lRow2, lCol + 1)).FormulaR1C1 = _
"=countif(r2c6:r" & lRow1 & "c6,rc[-1])"

Range(Cells(2, lCol), Cells(lRow2, lCol + 1)).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
Range(Cells(2, lCol), Cells(lRow2, lCol + 1)).ClearContents
Application.ScreenUpdating = True
End Sub[/VBA]

[jmm007]

run time error "9"

subscript out of range
third to last line

[VBA]Sub UniqueCountifMove()
Application.ScreenUpdating = False
Dim rngF As Range, lRow1 As Long, lCol As Integer, lRow2 As Long
lRow1 = Range("F2").End(xlDown).Row
Set rngF = Range("F2:F" & lRow1)
lCol = Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).Column
rngF.AdvancedFilter Action:=xlFilterCopy, copytorange:=Cells(2, lCol), _
unique:=True

Cells(2, lCol + 1) = "Count"
lRow2 = Cells(2, lCol).End(xlDown).Row
Range(Cells(3, lCol + 1), Cells(lRow2, lCol + 1)).FormulaR1C1 = _
"=countif(r2c6:r" & lRow1 & "c6,rc[-1])"

Range(Cells(2, lCol), Cells(lRow2, lCol + 1)).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
Range(Cells(2, lCol), Cells(lRow2, lCol + 1)).ClearContents
Application.ScreenUpdating = True
End Sub[/VBA]

[mbarron]

Please place the VBA tags around the code - edit your post, highlight the code and click the VBA button.

[vba]Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues[/vba]
Change the Sheet2 to the name of the sheet where you want the data to be placed.

[jmm007]

Solved and thank you.