hello to everybody,
I have same problem every month for my report in the part it should show data for current and previous month.
In the same worksheet I used to show data for current and previous month by branches conserning ranking by sales amount, for example Branch_A has highest volume of sales in current month and should be on first place in report table, the same Branch in previous month has some other level of volume that should be visible in report.
Easy part of report is fullfill current amounts by branches and do ranking, but the main problem is to get data for the same branch since ranking is changed from month to month (it is possible to have every month different ranking of branches by sales).
I used to get data for previous month by simple "viewing" and searcing for branch name and copy data for adequate (previous) month.
IT is holly hard work since there is to much branches to find and it is possible to make mistake by missmatching branch name.
I figure out that solution for my problem should be making VBA with Hlookup formula. But on the net there is so little help.
I found some example in the book "VBA & Macros for Microsoft Excel", chapter 8 from MrExcel but I did not know how to apply it to my problem
My table look like this:
A B C D E F
1 Branch_A Branch_B ::: And Other Branches
2 Jul-11 Jun-11 Jul-11 Jun-11
3Sales amount amount amount amount
4COGS amount amount amount amount
5... other positions
any idea?
the code from book is under (i need Hlookup)
Sub Page156Macro()
' This routine imports sales.csv to the data sheet
' Check to see whether any stores in column A are new
' If any are new, then add them to the StoreList table
Dim WSD As Worksheet
Dim WSM As Worksheet
Dim WB As Workbook
Set WB = ThisWorkbook
' Data is stored on the Data worksheet
Set WSD = ThisWorkbook.Worksheets("Data")
' StoreList is stored on a menu worksheet
Set WSM = ThisWorkbook.Worksheets("Menu")
' Here I commented out the code to import Sales.csv. We assume it had already been imported into data sheet.
' ' Open the file..This makes the csv file active
' Workbooks.Open Filename:="C:\Sales.csv"
' ' Copy the data to WSD and close
' Range("A1").CurrentRegion.Copy Destination:=WSD.Range("A1")
' ActiveWorkbook.Close SaveChanges:=False
' Activate the data sheet. Find a list of unique stores from column A
WSD.Activate
FinalRow = Cells(WSD.Rows.Count, 1).End(xlUp).Row
WSD.Range("A1").Resize(FinalRow, 1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=WSD.Range("Z1"), Unique:=True
' For all the unique stores, see whether they are in the
' current store list.
FinalStore = Range("Z" & WSD.Rows.Count).End(xlUp).Row
WSD.Range("AA1").Value = "There?"
WSD.Range("AA2:AA" & FinalStore).FormulaR1C1 = _
"=ISNA(VLOOKUP(RC[-1],StoreList,1,False))"
' Find the next row for a new store. Because StoreList starts in A1
' of the Menu sheet, find the next available row
' Note this corrects a typo in the book
NextRow = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row + 1
' Loop through the list of today ’s stores.If they are shown
' as missing, then add them at the bottom of the StoreList
For i = 2 To FinalStore
If Cells(i, 27).Value = True Then
ThisStore = Cells(i, 26).Value
WSM.Cells(NextRow, 1).Value = ThisStore
WSM.Cells(NextRow, 2).Value = InputBox(Prompt:="What is name of store " _
& ThisStore, Title:="New Store Found")
' Next line is missing from the book
NextRow = NextRow + 1
End If
Next i
' Delete the temporary list of stores in Z &AA
Range("Z1:AA" & FinalStore).Clear
' In case any stores were added, re-define StoreList name
FinalStore = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row
WSM.Range("A1:B" & FinalStore).Name = "StoreList"
' Use VLOOKUP to add StoreName to column B of the dataset
' Following line corrects typo in the book
Range("B1").EntireColumn.Insert
Range("B1").Value = "StoreName"
Range("B2:B" & FinalRow).FormulaR1C1 = "=VLOOKUP(RC1,StoreList,2,False)"
' Change Formulas to Values
Range("B2:B" & FinalRow).Value = Range("B2:B" & FinalRow).Value
Set WB = Nothing
Set WSD = Nothing
Set WSM = Nothing
End Sub
I have same problem every month for my report in the part it should show data for current and previous month.
In the same worksheet I used to show data for current and previous month by branches conserning ranking by sales amount, for example Branch_A has highest volume of sales in current month and should be on first place in report table, the same Branch in previous month has some other level of volume that should be visible in report.
Easy part of report is fullfill current amounts by branches and do ranking, but the main problem is to get data for the same branch since ranking is changed from month to month (it is possible to have every month different ranking of branches by sales).
I used to get data for previous month by simple "viewing" and searcing for branch name and copy data for adequate (previous) month.
IT is holly hard work since there is to much branches to find and it is possible to make mistake by missmatching branch name.
I figure out that solution for my problem should be making VBA with Hlookup formula. But on the net there is so little help.
I found some example in the book "VBA & Macros for Microsoft Excel", chapter 8 from MrExcel but I did not know how to apply it to my problem
My table look like this:
A B C D E F
1 Branch_A Branch_B ::: And Other Branches
2 Jul-11 Jun-11 Jul-11 Jun-11
3Sales amount amount amount amount
4COGS amount amount amount amount
5... other positions
any idea?
the code from book is under (i need Hlookup)
Sub Page156Macro()
' This routine imports sales.csv to the data sheet
' Check to see whether any stores in column A are new
' If any are new, then add them to the StoreList table
Dim WSD As Worksheet
Dim WSM As Worksheet
Dim WB As Workbook
Set WB = ThisWorkbook
' Data is stored on the Data worksheet
Set WSD = ThisWorkbook.Worksheets("Data")
' StoreList is stored on a menu worksheet
Set WSM = ThisWorkbook.Worksheets("Menu")
' Here I commented out the code to import Sales.csv. We assume it had already been imported into data sheet.
' ' Open the file..This makes the csv file active
' Workbooks.Open Filename:="C:\Sales.csv"
' ' Copy the data to WSD and close
' Range("A1").CurrentRegion.Copy Destination:=WSD.Range("A1")
' ActiveWorkbook.Close SaveChanges:=False
' Activate the data sheet. Find a list of unique stores from column A
WSD.Activate
FinalRow = Cells(WSD.Rows.Count, 1).End(xlUp).Row
WSD.Range("A1").Resize(FinalRow, 1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=WSD.Range("Z1"), Unique:=True
' For all the unique stores, see whether they are in the
' current store list.
FinalStore = Range("Z" & WSD.Rows.Count).End(xlUp).Row
WSD.Range("AA1").Value = "There?"
WSD.Range("AA2:AA" & FinalStore).FormulaR1C1 = _
"=ISNA(VLOOKUP(RC[-1],StoreList,1,False))"
' Find the next row for a new store. Because StoreList starts in A1
' of the Menu sheet, find the next available row
' Note this corrects a typo in the book
NextRow = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row + 1
' Loop through the list of today ’s stores.If they are shown
' as missing, then add them at the bottom of the StoreList
For i = 2 To FinalStore
If Cells(i, 27).Value = True Then
ThisStore = Cells(i, 26).Value
WSM.Cells(NextRow, 1).Value = ThisStore
WSM.Cells(NextRow, 2).Value = InputBox(Prompt:="What is name of store " _
& ThisStore, Title:="New Store Found")
' Next line is missing from the book
NextRow = NextRow + 1
End If
Next i
' Delete the temporary list of stores in Z &AA
Range("Z1:AA" & FinalStore).Clear
' In case any stores were added, re-define StoreList name
FinalStore = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row
WSM.Range("A1:B" & FinalStore).Name = "StoreList"
' Use VLOOKUP to add StoreName to column B of the dataset
' Following line corrects typo in the book
Range("B1").EntireColumn.Insert
Range("B1").Value = "StoreName"
Range("B2:B" & FinalRow).FormulaR1C1 = "=VLOOKUP(RC1,StoreList,2,False)"
' Change Formulas to Values
Range("B2:B" & FinalRow).Value = Range("B2:B" & FinalRow).Value
Set WB = Nothing
Set WSD = Nothing
Set WSM = Nothing
End Sub
