Tediously adding data to a table year-over-year, looking for pivot ideas..

[360]

I'm being tasked to provide a list of properties that a company owns, changing year over year, in tabular form. I have to source this info from the company's 10Ks, so its not all in one location and requires quite a bit of having to copy and paste the data (State and Property Name) into Excel for formatting before being able to add it to the table year over year. My manager wants to see it by year at the top (year of the 10K used), states on the left (the state rows increase as new properties get added but remain if properties are removed to show history over time). In the format he's looking for, with north of 10+ years of data, the table is getting enormous and I have to be extra cautious not to make any mistakes with the formatting (adding rows as necessary, etc). See example of what the table would appear like after adding year-over-year data across 3 years. The letters represent property names.. so by year 3 (2010) it shows the company still owns property A in AL, property B in AL was owned until 2009, property G has been owned since 2009, etc:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AK[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD][/TD]
[TD]G[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]GA[/TD]
[TD][/TD]
[TD][/TD]
[TD]H[/TD]
[/TR]
</tbody>[/TABLE]


I'd love to be able to paste the data I need into 3 columns (YEAR, STATE, PROPERTY) and then just pivot the entire set to show the list of properties over time as shown above but obv this isn't a pivot table's purpose (showing text in the values area).. and without macros I'm not sure there's a way to do this. I tried using PowerQuery but it displays errors for states with more than two properties using the "Do Not Aggregate" option. I feel as though there's a much easier way of accomplishing this without having to manually manicure the entire sheet to fit new yearly data each time. Anyone have any ideas? Again it HAS to be in the format of the table above.

 

[FDibbins]

What does the raw data look like?

Im thinking that maybe INDEX/MATCH might be the tool for this, but would need to see some sample data, to put anything together

 

[360]

What does the raw data look like?

Im thinking that maybe INDEX/MATCH might be the tool for this, but would need to see some sample data, to put anything together

Apologies for the delay, I tried attaching a worksheet with sample data but I've been unable to attach anything.. thought it was a browser issue but I'm not sure. Nevertheless, a friend provided some help with a macro. Will provide the solution here shortly. Thanks!

 

[FDibbins]

This forum does not allow uploading files, only images

 

[360]

Ah, understood. Well my friend keeps improving upon his original macro, for which each evolution has its own unique capabilities. Hopefully I can paste the code within here as text for you to see.

 

[FDibbins]

Can you share what some sample data would look like?

 

[360]

(1/2) The data I extract for the property list by state comes from clunky tables within the 10-K itself. It's a bit difficult to visualize the sample data here through text, best I can do are screenshots:

Sample from 10-K:

Step 1: Raw Data

Step 2: Clear Formatting

Step 3: Remove Other Columns

 

[360]

(2/2) Step 4: Remove Redundant Data

Step 5: Bolden States

Step 6: Sort Properties Alphabetically

*ignore "(2)" after Willow Creek, made a mistake there..


I'm talking about literally:
- copying and pasting these tables into a separate sheet in excel
- clearing out the formatting
- deleting the other columns that do not contain the state/property info (thankfully the state/property info is all in one column across all 10-Ks)
- using a filter to clear out blanks/spaces, redundant terms/anchors leaving me with just the states followed by their properties underneath
- bolding the state names (thankfully the states are in alphabetical order, unfortunately the properties aren't)
- sorting the property names for each state alphabetically

Mind you, this is just what I preferred the data to look like so that I could begin the process of transferring the data over to the 'Hospital List' sheet as best I could when I started this project.

 

[360]

Before the raw 10-K links were provided, I provided my friend with the following sample table data below (will try to communicate the intricacies over as best I can, so pls bear with me since I cannot attach Excel files):

Sample Data
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]ABC Community Hospital[/TD]
[TD]ABC Community Hospital[/TD]
[TD]ABC Community Hospital[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]DEF Medical Center Hospital[/TD]
[TD]DEF Medical Center Hospital[/TD]
[TD]DEF Medical Center Hospital[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD][/TD]
[TD]FEB Community Health Center[/TD]
[TD]FEB Community Health Center[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]GHI Memorial Hospital[/TD]
[TD]GHI Memorial Hospital[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD]JKL Medical Center[/TD]
[TD]JKL Medical Center[/TD]
[TD]JKL Medical Center[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD][/TD]
[TD]TIK Medical Hospital[/TD]
[TD]TIK Medical Hospital[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD]MNO Arizona Regional[/TD]
[TD]MNO Arizona Regional[/TD]
[TD]MNO Arizona Regional[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD][/TD]
[TD]CAB Regional Hospital[/TD]
[TD]CAB Regional Hospital[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]QRS Community Hospital[/TD]
[TD]QRS Community Hospital[/TD]
[TD]QRS Community Hospital[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[TD]TUV Medical Center[/TD]
[TD]TUV Medical Center[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[TD]WX County Hospital[/TD]
[TD]WX County Hospital[/TD]
[TD]WX County Hospital[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[TD]YZ Regional Hospital[/TD]
[TD]YZ Regional Hospital[/TD]
[TD]YZ Regional Hospital[/TD]
[/TR]
</tbody>[/TABLE]


..this basically means that the data (hospital list) for the first year (2001) appeared like this:

[TABLE="width: 215"]
<tbody>[TR]
[TD]Alabama[/TD]
[/TR]
[TR]
[TD]ABC Community Hospital[/TD]
[/TR]
[TR]
[TD]DEF Medical Center Hospital[/TD]
[/TR]
[TR]
[TD]GHI Memorial Hospital[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[/TR]
[TR]
[TD]JKL Medical Center[/TD]
[/TR]
[TR]
[TD]MNO Arizona Regional[/TD]
[/TR]
[TR]
[TD]California[/TD]
[/TR]
[TR]
[TD]QRS Community Hospital[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[/TR]
[TR]
[TD]TUV Medical Center[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD]WX County Hospital[/TD]
[/TR]
[TR]
[TD]YZ Regional Hospital[/TD]
[/TR]
</tbody>[/TABLE]


..the second year (2002) like this:

[TABLE="width: 215"]
<tbody>[TR]
[TD]Alabama[/TD]
[/TR]
[TR]
[TD]ABC Community Hospital[/TD]
[/TR]
[TR]
[TD]DEF Medical Center Hospital[/TD]
[/TR]
[TR]
[TD]FEB Community Health Center[/TD]
[/TR]
[TR]
[TD]GHI Memorial Hospital[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[/TR]
[TR]
[TD]JKL Medical Center[/TD]
[/TR]
[TR]
[TD]TIK Medical Hospital[/TD]
[/TR]
[TR]
[TD]MNO Arizona Regional[/TD]
[/TR]
[TR]
[TD]California[/TD]
[/TR]
[TR]
[TD]CAB Regional Hospital[/TD]
[/TR]
[TR]
[TD]QRS Community Hospital[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[/TR]
[TR]
[TD]TUV Medical Center[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD]WX County Hospital[/TD]
[/TR]
[TR]
[TD]YZ Regional Hospital[/TD]
[/TR]
</tbody>[/TABLE]


..and the third year (2003) like this:

[TABLE="width: 215"]
<tbody>[TR]
[TD]Alabama[/TD]
[/TR]
[TR]
[TD]ABC Community Hospital[/TD]
[/TR]
[TR]
[TD]DEF Medical Center Hospital[/TD]
[/TR]
[TR]
[TD]FEB Community Health Center[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[/TR]
[TR]
[TD]JKL Medical Center[/TD]
[/TR]
[TR]
[TD]TIK Medical Hospital[/TD]
[/TR]
[TR]
[TD]MNO Arizona Regional[/TD]
[/TR]
[TR]
[TD]California[/TD]
[/TR]
[TR]
[TD]CAB Regional Hospital[/TD]
[/TR]
[TR]
[TD]QRS Community Hospital[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD]WX County Hospital[/TD]
[/TR]
[TR]
[TD]YZ Regional Hospital[/TD]
[/TR]
</tbody>[/TABLE]

So the table above visually represents the lists from year to year, whether current, old, or new properties. My friend provided a file with 3 macros and the following info based on a file that appeared like this screenshot:

Okay, I managed to work out a sample file.
First, your manager may require the output like you said and it looks like that but there is a hidden Column B, which you may unhide but it will be hidden after processing unless you disable that line of code.
The reason I did this is to solve the sorting of the results list after a new state has been added or an Item (as I called it in my code)

There is one macro you need to run: ProcessOne()

It will prompt you for a year value which by default is the current year - 1 since I have made the assumption that you run this list at the beginning of every new year

The Worksheets with the Source List are expected to be named yyyy 10K-Data

The Macro will prompt for a year and the worksheet will be used that has that name: e.g. 2001 10-K-Data (Case sensitive!!!)

The code does not contain an error check to see if the worksheet exists so you will have to fine-tune that.

In the Source List ONLY THE STATE NAMES MUST BE BOLD FONT this is use to check if the value is a state or an establishment and the grouping of establishment per state is your responsibility.

Run the macro three times, 2001, 2002 and 2003 to see the results.

 

[360]

Macros provided:

ProcessOne

Public Sub ProcessOne()
Dim fYear As Long
tryagain:
fYear = 0
fYear = Application.InputBox("Enter processing year:", Title:="Which year?", Default:=Year(Date) - 1, Type:=1)
If fYear = 0 Then Exit Sub
If fYear < 2001 Then GoTo tryagain
If fYear >= Year(Date) Then GoTo tryagain
Select Case MsgBox("Process list for '" & fYear & "'?", vbQuestion + vbYesNoCancel, "Continue?")
Case Is = vbNo: GoTo tryagain
Case Is = vbCancel: Exit Sub
End Select


Application.ScreenUpdating = False
If ProcessSourceList(fYear:=fYear) = True Then SortResults
Application.ScreenUpdating = True


End Sub

ProcessSourceList

Public Function ProcessSourceList(fYear As Long) As Boolean
Dim ws As Worksheet
Dim wsT As Worksheet


Set ws = Worksheets(fYear & Space(1) & "10-K Data")
Set wsT = Worksheets("Hospital List")


Dim lstRow As Long
Dim lstCol As Long
Dim myState As String
Dim myItem As String
Dim xRow As Long
Dim iCol As Long
Dim sRow As Long
Dim iRow As Long
'Dim fYear As Long
Dim fRng As Range


lstRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
myState = vbNullString: myItem = vbNullString


wsT.Activate
Range("B:B").EntireColumn.Hidden = False
GoSub findYearColumn


For xRow = 1 To lstRow
If ws.Cells(xRow, 1).Font.Bold = True Then
myState = ws.Cells(xRow, 1).Value
GoSub findStateRow
Else
myItem = ws.Cells(xRow, 1).Value
GoSub findItemRow
If iRow > 0 Then
If iCol > 2 Then
If wsT.Cells(iRow, 2).Value <> myItem And wsT.Cells(iRow, 2).Value <> "" Then
iRow = wsT.Cells(Rows.Count, 1).End(xlUp).Row + 1
sRow = iRow
GoSub addState
End If
End If
Else
If wsT.Cells(sRow, 2).Value = "" Then
iRow = sRow
Else
iRow = wsT.Cells(Rows.Count, 1).End(xlUp).Row + 1
sRow = iRow
GoSub addState
End If
End If
wsT.Cells(iRow, 2).Value = myItem
wsT.Cells(iRow, iCol).Value = myItem
End If
Next xRow
Cells.Columns.AutoFit
Range("B:B").EntireColumn.Hidden = True
ProcessSourceList = True
Exit Function


'*******************************************************************************
'* Supporting routines
addState:
With wsT.Cells(sRow, 1)
.Value = myState
.Font.Bold = True
End With
Return


findStateRow:
sRow = 0
With wsT.Range("A:A")
Set fRng = .Find(What:=myState, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not fRng Is Nothing Then
sRow = fRng.Row
Else
sRow = wsT.Cells(Rows.Count, 1).End(xlUp).Row + 1
GoSub addState
End If
End With
Return


findItemRow:
iRow = 0
Debug.Print wsT.Range("A1").SpecialCells(xlCellTypeLastCell).Address, wsT.Range("$A$1:" & wsT.Range("A1").SpecialCells(xlCellTypeLastCell).Address).Address
With wsT.Range("$A$1:" & wsT.Range("A1").SpecialCells(xlCellTypeLastCell).Address)
Set fRng = .Find(What:=myItem, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not fRng Is Nothing Then
iRow = fRng.Row
End If
End With
Return


findYearColumn:
lstCol = wsT.Cells(1, Columns.Count).End(xlToLeft).Column
With wsT.Range(Cells(1, 2), Cells(1, lstCol))
Set fRng = .Find(What:=fYear, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not fRng Is Nothing Then
iCol = fRng.Column
Else
iCol = lstCol + 1
wsT.Cells(1, iCol).Value = fYear
End If
End With
Return


End Function

SortResults

Public Sub SortResults()
'
Dim lastRow As Long
Dim srtRange As String
Sheets("Hospital List").Select
lastRow = Range("A1").CurrentRegion.Rows.Count
Range("A1").CurrentRegion.Select
srtRange = Selection.Address
ActiveWorkbook.Worksheets("Hospital List").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hospital List").Sort.SortFields.Add Key:=Range( _
"A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Hospital List").Sort.SortFields.Add Key:=Range( _
"B2:B" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Hospital List").Sort
.SetRange Range(srtRange)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C2").Select
End Sub