Creat a quick link to combine data

[mbotros1]

i downloaded a spreadsheet with multiple same names such as

Name Week ending Days off
John Smith 1/31 2
John Smith 2/7 1
John Smith 2/7 3
Peter Sun 1/31 1
Peter Sun 2/7 2
Andy Mann 3/14 3

if i wan to sum these up by name, what's the best way to do that?

Thanks,
Max

 

[hiker95]

mbotros1,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Are you using a PC or a Mac?


Sample raw data:

Excel 2007
	A	B	C	D	E	F	G
1	Name	Week ending	Days off
2	John Smith	1/31	2
3	John Smith	2/7	1
4	John Smith	2/7	3
5	Peter Sun	1/31	1
6	Peter Sun	2/7	2
7	Andy Mann	3/14	3
8
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G
1	Name	Week ending	Days off			Name	Totals
2	John Smith	1/31	2			John Smith	6
3	John Smith	2/7	1			Peter Sun	3
4	John Smith	2/7	3			Andy Mann	3
5	Peter Sun	1/31	1
6	Peter Sun	2/7	2
7	Andy Mann	3/14	3
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MergeDupesColA_SumColC()
' hiker95, 03/17/2014, ME764802
Dim a As Variant, o, p, i As Long, d As Object
a = Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = 1
For i = 2 To UBound(a, 1)
  d(a(i, 1)) = d(a(i, 1)) + a(i, 3)
Next i
Columns("F:G").ClearContents
Cells(1, 6).Resize(, 2).Value = Array("Name", "Totals")
o = Application.Transpose(Array(d.Keys, d.Items))
ReDim p(1 To UBound(o, 1), 1 To 2)
For i = 1 To UBound(o, 1)
  p(i, 1) = o(i, 1)
  p(i, 2) = o(i, 2)
Next i
Cells(2, 6).Resize(UBound(p, 1), UBound(p, 2)) = p
Columns("F:G").AutoFit
End Sub

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MergeDupesColA_SumColC macro.

 

[mbotros1]

I'm using Excel 2010, PC.

mbotros1,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Are you using a PC or a Mac?


Sample raw data:

Excel 2007

	A	B	C	D	E	F	G
1	Name	Week ending	Days off
2	John Smith	1/31	2
3	John Smith	2/7	1
4	John Smith	2/7	3
5	Peter Sun	1/31	1
6	Peter Sun	2/7	2
7	Andy Mann	3/14	3
8

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

After the macro:

Excel 2007

	A	B	C	D	E	F	G
1	Name	Week ending	Days off			Name	Totals
2	John Smith	1/31	2			John Smith	6
3	John Smith	2/7	1			Peter Sun	3
4	John Smith	2/7	3			Andy Mann	3
5	Peter Sun	1/31	1
6	Peter Sun	2/7	2
7	Andy Mann	3/14	3
8

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub MergeDupesColA_SumColC()
' hiker95, 03/17/2014, ME764802
Dim a As Variant, o, p, i As Long, d As Object
a = Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = 1
For i = 2 To UBound(a, 1)
  d(a(i, 1)) = d(a(i, 1)) + a(i, 3)
Next i
Columns("F:G").ClearContents
Cells(1, 6).Resize(, 2).Value = Array("Name", "Totals")
o = Application.Transpose(Array(d.Keys, d.Items))
ReDim p(1 To UBound(o, 1), 1 To 2)
For i = 1 To UBound(o, 1)
  p(i, 1) = o(i, 1)
  p(i, 2) = o(i, 2)
Next i
Cells(2, 6).Resize(UBound(p, 1), UBound(p, 2)) = p
Columns("F:G").AutoFit
End Sub

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the MergeDupesColA_SumColC macro.

 

[mbotros1]

Thanks so much for your help, i ran it, but here are my challenges

1. i need the result to be posted in a separate tab not in the same tab where the original data is
2- there are 2 sests of days off ( vacation or sick days), so in other words consider that one in my originl email repersents sick days off, there is another column should say vaction days off (4 columns instead of 3).

I would appreciate if you could help me.

Thanks,
Max

 

[hiker95]

mbotros1,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.

1. i need the result to be posted in a separate tab not in the same tab where the original data is
2- there are 2 sests of days off ( vacation or sick days), so in other words consider that one in my originl email repersents sick days off, there is another column should say vaction days off (4 columns instead of 3).

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

Can you post screenshots of the actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel.com | Excel Resources | Excel Seminars | Excel Products

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[mbotros1]

here is the link https://app.box.com/s/epdxaajrlwit46b5rwtw

file (book 31)I'm trying to capture only codes for sick day (301, 302, 304) and vacation ( 402, 403, 407, 408) qnd put populate it in the vacation master file.

employees are repetitive in book 31 ( i changed the names with # and trying to repeat some # as some names are recitative)
hope i'm clear.

 

[hiker95]

mbotros1,

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

Thanks for the new workbook.

But, I do not need almost 18,000 rows of raw data to work with - just a sampling of the new data structure.

And, you did not provide an additional worksheet (manually formatted by you) for the results you are now looking for.

Please supply another workbook per the above.

 

[mbotros1]

ok, i uploaded them again.

here is the link https://app.box.com/s/kpx71mmacjit6bpaupwh
and https://app.box.com/s/9jtbjyzpjyx1poko0g12

i inserted the final result in vacation master file

my goal is to combine all types/categories of sick days and vacation days and populate them in vacation master file for each employee at a time.

please feel free to ask me if i'm still not too clear.

thanks again for your help

 

[hiker95]

mbotros1,

Thanks for the two workbooks.

We have gone from a fairly simple single worksheet project, to a project that uses two workbooks.

I have not done any multiple workbook projects for a very long time.


If we were working in one workbook, going from Sheet1 to worksheet Vacation & Sick Master, this would be easy.


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[mbotros1]

bump