Excel Data Merging/ data Analysis

[akpflow]

Good day people. Thank you for your support so far...
I need to merge some data values from some spreadsheets to another..
Table A for Year 2013

Family ID	City	State	Postal Code	Amount

<tbody>
</tbody>

Table B for Year 2014

Family ID	City	State	Postal Code	Amount

<tbody>
</tbody>

Merged Table

Family ID	City	State	2013 Amount	2014 Amount	2015 Amount	2016 Amount

<tbody>
</tbody>

Question 2:
How can I use excel to design a line graph to show amount vs time in a graph?



Thank you

 

[michaelsmith559]

I created five sheets named: Table A, Table B, Table C, Table D, Merged using the years you posted. I tried to have excel do the line graph automatically for you, but I think I have a bug in excel 2013 or windows update. Nothing I tried would create the line graph using vba. Vba will create the chart, and place it, but it will not do the xlLineMarkers. I have to manually click on the data points and change it. Hopefully someone on here will be able to help with that part of it. I have included what the macro recorder did in case it may help you figure something out. I will post screenshots of how I setup each sheet and show you the merged sheet after macro runs.

Excel Workbook
	A	B	C	D	E	F	G
1	Family Id	City	State	Postal Code	Amount	Year
2	Smith	Anywhere	Ga	12345	$5.00	2013
3	Smyth	Anywhere	Ga	12345	$3.00	2013
4	Smith	Anywhere	Ga	12345	$1.00	2013
5	Smyth	Anywhere	Ga	12345	$12.00	2013
6	Smith	Anywhere	Ga	12345	$7.00	2013
7
Table A

Excel Workbook
	A	B	C	D	E	F	G
1	Family Id	City	State	Postal Code	Amount	Year
2	Smith	Anywhere	Sc	12345	$7.00	2014
3	Smyth	Anywhere	Sc	12345	$6.00	2014
4	Smith	Anywhere	Sc	12345	$9.00	2014
5	Smyth	Anywhere	Sc	12345	$4.00	2014
6	Smith	Anywhere	Sc	12345	$5.00	2014
7
Table B

Excel Workbook
	A	B	C	D	E	F	G
1	Family Id	City	State	Postal Code	Amount	Year
2	Smith	Anywhere	Ky	12345	$9.00	2015
3	Smyth	Anywhere	Ky	12345	$7.00	2015
4	Smith	Anywhere	Ky	12345	$8.00	2015
5	Smyth	Anywhere	Ky	12345	$12.00	2015
6	Smith	Anywhere	Ky	12345	$5.00	2015
7
Table C

Excel Workbook
	A	B	C	D	E	F	G
1	Family Id	City	State	Postal Code	Amount	Year
2	Smith	Anywhere	Md	12345	$15.00	2016
3	Smyth	Anywhere	Md	12345	$10.00	2016
4	Smith	Anywhere	Md	12345	$12.00	2016
5	Smyth	Anywhere	Md	12345	$3.00	2016
6	Smith	Anywhere	Md	12345	$9.00	2016
7
Table D

Excel Workbook
	A	B	C	D	E	F	G
1	Family Id	City	State	Year	Amount
2
3
Merged

After code runs:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Family Id	City	State	Year	Amount		Year	Year	Amount
2	Smith	Anywhere	Ga	2013	$5.00		2013	2013	$5.00
3	Smyth	Anywhere	Ga	2013	$3.00		2014		$3.00
4	Smith	Anywhere	Ga	2013	$1.00		2015		$1.00
5	Smyth	Anywhere	Ga	2013	$12.00		2016		$12.00
6	Smith	Anywhere	Ga	2013	$7.00				$7.00
7	Smith	Anywhere	Sc	2014	$7.00			2014	$7.00
8	Smyth	Anywhere	Sc	2014	$6.00				$6.00
9	Smith	Anywhere	Sc	2014	$9.00				$9.00
10	Smyth	Anywhere	Sc	2014	$4.00				$4.00
11	Smith	Anywhere	Sc	2014	$5.00				$5.00
12	Smith	Anywhere	Ky	2015	$9.00			2015	$9.00
13	Smyth	Anywhere	Ky	2015	$7.00				$7.00
14	Smith	Anywhere	Ky	2015	$8.00				$8.00
15	Smyth	Anywhere	Ky	2015	$12.00				$12.00
16	Smith	Anywhere	Ky	2015	$5.00				$5.00
17	Smith	Anywhere	Md	2016	$15.00			2016	$15.00
18	Smyth	Anywhere	Md	2016	$10.00				$10.00
19	Smith	Anywhere	Md	2016	$12.00				$12.00
20	Smyth	Anywhere	Md	2016	$3.00				$3.00
21	Smith	Anywhere	Md	2016	$9.00				$9.00
22
Merged

Sub Merge_and_Chart()
Application.ScreenUpdating = False
Dim i As Long, j As Long, mylastrow As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
Dim myval As String, lastcolumn As Long
Dim add1 As Long, add2 As Long
Dim xrow As Long, lastrow As Long, rng As Range

For i = 1 To Worksheets.Count - 1
Sheets(i).Activate
With Sheets(i)
    Set rng1 = Sheets(i).Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set rng2 = Sheets(i).Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    Set rng3 = Sheets(i).Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)
    Set rng5 = Sheets(i).Range("E2:E" & Cells(Rows.Count, 5).End(xlUp).Row)
    Set rng4 = Sheets(i).Range("F2:F" & Cells(Rows.Count, 6).End(xlUp).Row)
End With

Sheets("Merged").Activate
    
    Sheets("Merged").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(rng1.Rows.Count, rng1.Columns.Count).Cells.Value = rng1.Cells.Value
    Sheets("Merged").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Resize(rng2.Rows.Count, rng2.Columns.Count).Cells.Value = rng2.Cells.Value
    Sheets("Merged").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Resize(rng3.Rows.Count, rng3.Columns.Count).Cells.Value = rng3.Cells.Value
    Sheets("Merged").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Resize(rng4.Rows.Count, rng4.Columns.Count).Cells.Value = rng4.Cells.Value
    Sheets("Merged").Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Resize(rng5.Rows.Count, rng5.Columns.Count).Cells.Value = rng5.Cells.Value
    
Next

Range("D:D").Copy Range("G1")
Range("G2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.RemoveDuplicates Columns:=Array(1), Header:=xlNo
Selection.Sort key1:=ActiveCell, order1:=xlAscending
Range("H1").Value = "Year"
Range("I1").Value = "Amount"
Range("F1").Activate

mylastrow = Cells(Rows.Count, 7).End(xlUp).Row

For j = 2 To mylastrow

myval = Cells(j, 7).Value
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = _
    Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Offset(0, -4).Column
add1 = Columns(4).Find(What:=myval, LookIn:=xlValues, LookAt:=xlWhole).Row
xrow = add1

Do

If Cells(xrow + 1, 4).Value <> myval Then
    add2 = xrow
    Exit Do
Else
    xrow = xrow + 1
End If

Loop Until xrow = lastrow + 1

Range(Cells(add1, lastcolumn), Cells(add2, lastcolumn)).Copy Cells(Rows.Count, 9).End(xlUp).Offset(1, 0)
Range(Cells(add1, 4), Cells(add1, 4)).Copy Cells(add1, 8)

Next

Range("H1:I" & Cells(Rows.Count, 9).End(xlUp).Row).Select
Set rng = Selection
    ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=rng
    ActiveSheet.Shapes("Chart 1").IncrementLeft 38.25
    ActiveSheet.Shapes("Chart 1").IncrementTop -166.5
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.1979166667, msoFalse, _
        msoScaleFromTopLeft

Range("F1").Select

Application.ScreenUpdating = True
End Sub