The UNION function allows you to take multiple lists in an excel file with different orders of names and then put them into a common list. The union function allows you to put together financial statements with different titles in different years. Using this method you can make the process of creating a historic database of financial statements easier. This page includes demonstrates how to use the function and includes technical documentation of the function. To access the UNION function you need the read pdf file open. You can then copy the function into your file using the ALT F8 method as any UDF function. The Read Pdf to Excel file can be downloaded by pressing the button below. This page also covers reading data from the SEC website into excel for which you can use an alternative file.

** **

## Using the Union Function to Put Together Financial Statements

Like many functions, the UNION function is an array function. This means the output does not go into one cell but rather into a series of cells. To operate the UNION function you should leave a long space and then select the long space. After that, you begin typing the UNION function. Then like with any function press the tab key. After that, you can select multiple other columns (or rows) that will be combined into one single column, At the end press the SHIFT, CNTL, ENTER sequence.

Here is a step by step review of the union function:

Step 1: Insert some columns at the left of the sheet

Step 2: Use the Union Function which is an array function (SHIFT, CNTL, ENTER)

Step 3: Match the Cell from the UNION function list with original list of names for the year and put 0 at the end

Step 4: Combine the INDEX with the MATCH function and you will have a consistent set of columns

The video below is lengthy and bad but it goes through this.

The second video demonstrates the VBA code that is used to create the UNION function. This video is more technical and has various sort routined.

.

## Technical Details of the Union Function

The insert below shows the code for creating the UNION Function. You can copy this function to your sheets like you can copy any UDF. Just select the code below and copy it to a VBA module.

The code for this union function is shown in the box below:

Function union(range1, range2, Optional range3, Optional range4, Optional range5, Optional range6, Optional range7, _ Optional range8, Optional range9, Optional range10, Optional range11, Optional range12) As Variant Dim output(1000) As Variant ' output variables - for aggregate list Dim output1(1000) As Variant ' variable with code number to maintain order Dim output3(1000, 1) As Variant ' ultimate output variable GoTo missing_test: missing_return: GoTo num_range: ' Get the total number for the loop through counting variables num_range_return: ' Where to come back after getting numbers GoTo accumulate: accumulate_return: ' sort the output array without the numbers so that can remove duplicates For i = 1 To tot_num For j = i To tot_num If output(j) < output(i) Then str1 = output(i) str2 = output(j) output(i) = str2 output(j) = str1 str_1 = output1(i) str_2 = output1(j) output1(i) = str_2 output1(j) = str_1 End If Next j Next i ' eliminate duplicates from the output array without the numbers Count = 2 For i = 2 To tot_num Step 1 ' master list If output(Count) = output(Count - 1) Then ' if last value = prior value For j = Count To tot_num Step 1 ' push everything up If j > 1 Then output(j - 1) = output(j) If j > 1 Then output1(j - 1) = output1(j) Next j Count = Count - 1 End If Count = Count + 1 Next i revised_tot = Count - 1 ' re-sort with number For i = 1 To revised_tot For j = i To revised_tot If output1(j) < output1(i) Then str1 = output1(i) str2 = output1(j) output1(i) = str2 output1(j) = str1 str_1 = output(i) str_2 = output(j) output(i) = str_2 output(j) = str_1 End If Next j Next i For i = 1 To revised_tot output3(i, 1) = output(i) Next i union = output3 Exit Function missing_test: ' Create test for optional variables when you enter a variable range3_option = True range4_option = True range5_option = True range6_option = True range7_option = True range8_option = True range9_option = True range10_option = True range11_option = True range12_option = True If IsMissing(range3) Then range3_option = False If IsMissing(range4) Then range4_option = False If IsMissing(range5) Then range5_option = False If IsMissing(range6) Then range6_option = False If IsMissing(range7) Then range7_option = False If IsMissing(range8) Then range8_option = False If IsMissing(range9) Then range9_option = False If IsMissing(range10) Then range10_option = False If IsMissing(range11) Then range11_option = False If IsMissing(range12) Then range12_option = False GoTo missing_return: ' count the number in the series num_range: num1 = range1.Count num2 = range2.Count If range3_option Then num3 = range3.Count If range4_option Then num4 = range4.Count If range5_option Then num5 = range5.Count If range6_option Then num6 = range6.Count If range7_option Then num7 = range7.Count If range8_option Then num8 = range8.Count If range9_option Then num9 = range9.Count If range10_option Then num10 = range10.Count If range11_option Then num11 = range11.Count If range12_option Then num12 = range12.Count tot_num = num1 + num2 + num3 + num4 + num5 + num6 + num7 + num8 + num9 + num10 + num11 + num12 revised_tot = tot_num GoTo num_range_return: accumulate: ' Accumulate the data into output and output1 For i = 1 To num1 ' accumulate into a single array Count = Count + 1 output1(Count) = i + 100 & " " & range1(i) ' use code numbers to maintain the order of the numbers output(Count) = range1(i) ' make a big array with all of the ranges Next i For i = 1 To num2 Count = Count + 1 output1(Count) = i + 100 & " " & range2(i) output(Count) = range2(i) Next i If range3_option Then For i = 1 To num3 Count = Count + 1 output1(Count) = i + 100 & " " & range3(i) output(Count) = range3(i) Next i End If If range4_option Then ' only do when passed the missing test for range For i = 1 To num4 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range4(i) ' use code numbers to maintain the order of the numbers output(Count) = range4(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range5_option Then ' only do when passed the missing test for range For i = 1 To num5 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range5(i) ' use code numbers to maintain the order of the numbers output(Count) = range5(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range6_option Then ' only do when passed the missing test for range For i = 1 To num6 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range6(i) ' use code numbers to maintain the order of the numbers output(Count) = range6(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range7_option Then ' only do when passed the missing test for range For i = 1 To num7 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range7(i) ' use code numbers to maintain the order of the numbers output(Count) = range7(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range8_option Then ' only do when passed the missing test for range For i = 1 To num8 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range8(i) ' use code numbers to maintain the order of the numbers output(Count) = range8(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range9_option Then ' only do when passed the missing test for range For i = 1 To num9 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range9(i) ' use code numbers to maintain the order of the numbers output(Count) = range9(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range10_option Then ' only do when passed the missing test for range For i = 1 To num10 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range10(i) ' use code numbers to maintain the order of the numbers output(Count) = range10(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range11_option Then ' only do when passed the missing test for range For i = 1 To num11 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range11(i) ' use code numbers to maintain the order of the numbers output(Count) = range11(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range If range12_option Then ' only do when passed the missing test for range For i = 1 To num12 ' accumulate into an aggregate single array Count = Count + 1 output1(Count) = i + 100 & " " & range12(i) ' use code numbers to maintain the order of the numbers output(Count) = range12(i) ' make a big array with all of the ranges Next i End If ' end of missing test on range tot_num = Count revised_tot = tot_num GoTo accumulate_return: End Function Sub A_union_function_help() UserForm11.Show GoTo skip MsgBox _ Chr(1) & Chr(2) & Chr(3) & Chr(4) & Chr(5) & Chr(6) & Chr(7) & Chr(8) & Chr(9) & Chr(10) & Chr(11) & " Test Chars " & Chr(13) & Chr(13) & Chr(13) & _ Chr(11) & Chr(12) & Chr(13) & Chr(14) & Chr(15) & Chr(16) & Chr(17) & Chr(18) & Chr(19) & Chr(20) & Chr(21) & " Find the A_function_help macro and copy entire module to you workbook" & Chr(13) & Chr(13) & Chr(13) & _ Chr(21) & Chr(22) & Chr(23) & Chr(24) & Chr(25) & Chr(26) & Chr(27) & Chr(28) & Chr(29) & Chr(30) & Chr(31) & " Find the A_function_help macro and copy entire module to you workbook" & Chr(13) & Chr(13) & Chr(13) & _ " Please make sure the Option Base 1 is at the top " & Chr(13) & Chr(13) & Chr(13) & _ " To Sort, for now you must copy and paste special " & Chr(13) & Chr(13) & Chr(13) & _ " There is an INDMAT -- Index and Match Function. This can be used with entire columns or rows" & Chr(13) & Chr(13) & Chr(13) skip: MsgBox _ " Find the UNION functions (See the Button Below). COPY entire module to you workbook" & Chr(13) & Chr(13) & Chr(13) & _ " Make sure the Option Base 1 is at the top " & Chr(13) & Chr(13) & Chr(13) & _ " Use the UNION function to get a TITLE List -- Make Similar Titles " & Chr(13) & Chr(13) & Chr(13) & _ " The UNION is only for getting a good master list of Similar Titles " & Chr(13) & Chr(13) & Chr(13) & _ " Use the INDMAT function for the Numbers -- Enter the Account Title, the Master Title and the Numbers " & Chr(13) & Chr(13) & Chr(13) & _ " To Sort, for now you must copy and paste special " & Chr(13) & Chr(13) & Chr(13) & _ " The INDMAT -- Index and Match Function makes things easier. This can be used with entire columns or rows" & Chr(13) & Chr(13) & Chr(13), , _ " MAKING THE UNION AND INDMAT WORK FOR READING FINANCIALS" MsgBox _ " After Reading in the Financials, you should do some PREPARATION " & Chr(13) & Chr(13) & Chr(13) & _ " 1. Put the Financials in the Same Sheet " & Chr(13) & Chr(13) & Chr(13) & _ " 2. Put the years in Order " & Chr(13) & Chr(13) & Chr(13) & _ " 3. Adjust Titles to Make Them Similar " & Chr(13) & Chr(13) & Chr(13) & _ " 4. Apply the UNION function for a comprehensive set of titles " & Chr(13) & Chr(13) & Chr(13) & _ " 5. Use the INDMAT function with locked columns and copy down " & Chr(13) & Chr(13) & Chr(13) & _ " The INDMAT -- Index and Match Function makes things easier. This can be used with entire columns or rows" & Chr(13) & Chr(13) & Chr(13), , _ " MAKING THE UNION AND INDMAT WORK FOR READING FINANCIALS" End Sub

## Technical Details of the INDMAT

The insert below shows the code for creating the INDMAT Function. You can copy this function to your sheets like for the other functions.

Function indmat(lookup_value, master_list, values) As Single ' This function does the index and match together Dim num As Single num = 150 For i = 1 To num ' loop around a given number If lookup_value = master_list(i) Then indmat = values(i) Exit Function End If Next i ' On Error GoTo no_value: ' match1 = WorksheetFunction.Match(lookup_value, master_list, 0) ' indmat = WorksheetFunction.Index(values, match1) ' Exit Function no_value: indmat = 0 End Function