anthonyexcel
Active Member
- Joined
- Jun 10, 2011
- Messages
- 258
- Office Version
- 365
- Platform
- Windows
Good afternoon. This is just some sample data that I have the real data has about 250000 rows and about 100 columns.
I have data on sheet1 and on sheet 2. Sheet1 is a run of student absences, sheet 2 is a summary of those student absences.
Sheet1 columns J:S are always the same. What I am looking to do is to match the ID on sheet 1 to sheet 2 and when
I have match, copy the data over (columns J:S) from sheet 1 to sheet 2 pasting it in column G.Please see below.
I was using this formula but the data is too large and I am looking to automate it with VBA,
=VLOOKUP($A2,Sheet1!$A$1:$S$12,COLUMN(Sheet1!J2),0)
Sheet 1
Sheet 2
Sheet 2 Final
I have data on sheet1 and on sheet 2. Sheet1 is a run of student absences, sheet 2 is a summary of those student absences.
Sheet1 columns J:S are always the same. What I am looking to do is to match the ID on sheet 1 to sheet 2 and when
I have match, copy the data over (columns J:S) from sheet 1 to sheet 2 pasting it in column G.Please see below.
I was using this formula but the data is too large and I am looking to automate it with VBA,
=VLOOKUP($A2,Sheet1!$A$1:$S$12,COLUMN(Sheet1!J2),0)
Sheet 1
Code:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]ID[/TH]
[TH]First Name[/TH]
[TH]Last Name[/TH]
[TH]Att School[/TH]
[TH]Current School[/TH]
[TH]ATT_DATE[/TH]
[TH]DESCRIPTION[/TH]
[TH]ENTRYDATE[/TH]
[TH]EXITDATE[/TH]
[TH]Letter 1[/TH]
[TH]Comment[/TH]
[TH]Letter 2[/TH]
[TH]Comment[/TH]
[TH]Letter 3[/TH]
[TH]Comment[/TH]
[TH]Letter 4[/TH]
[TH]Comment[/TH]
[TH]Letter 5[/TH]
[TH]Comment[/TH]
[/TR]
[TR]
[TD]11111[/TD]
[TD]xxxx[/TD]
[TD]xxxx[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]9/12/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]9/14/2014[/TD]
[TD]Jack sent[/TD]
[TD]9/23/2014[/TD]
[TD]Jack sent[/TD]
[TD]11/16/2014[/TD]
[TD]Jill sent[/TD]
[TD]12/9/2014[/TD]
[TD]Mike sent[/TD]
[TD]12/12/2014[/TD]
[TD]Mike sent[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]xxxx[/TD]
[TD]xxxx[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]9/13/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]9/14/2014[/TD]
[TD]Jack sent[/TD]
[TD]9/23/2014[/TD]
[TD]Jack sent[/TD]
[TD]11/16/2014[/TD]
[TD]Jill sent[/TD]
[TD]12/9/2014[/TD]
[TD]Mike sent[/TD]
[TD]12/12/2014[/TD]
[TD]Mike sent[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]xxxx[/TD]
[TD]xxxx[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]9/14/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]9/14/2014[/TD]
[TD]Jack sent[/TD]
[TD]9/23/2014[/TD]
[TD]Jack sent[/TD]
[TD]11/16/2014[/TD]
[TD]Jill sent[/TD]
[TD]12/9/2014[/TD]
[TD]Mike sent[/TD]
[TD]12/12/2014[/TD]
[TD]Mike sent[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]yyyy[/TD]
[TD]yyyy[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]9/12/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/1/2014[/TD]
[TD]Mark sent[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]yyyy[/TD]
[TD]yyyy[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]9/13/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/1/2014[/TD]
[TD]Mark sent[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]zzzz[/TD]
[TD]zzzz[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]9/12/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/14/2014[/TD]
[TD][/TD]
[TD]11/23/2014[/TD]
[TD][/TD]
[TD]12/16/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44444[/TD]
[TD]aaaa[/TD]
[TD]aaaa[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]9/16/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]11/2/2014[/TD]
[TD]AL sent[/TD]
[TD]11/16/2014[/TD]
[TD]AL sent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/17/2014[/TD]
[TD]Joe sent[/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD]dddd[/TD]
[TD]dddd[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]9/9/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]9/11/2014[/TD]
[TD]Sam sent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/9/2014[/TD]
[TD]Ted sent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]77777[/TD]
[TD]ffff[/TD]
[TD]ffff[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]9/12/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]1/15/2015[/TD]
[TD]Dave sent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]88888[/TD]
[TD]gggg[/TD]
[TD]gggg[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]9/12/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]10/4/2014[/TD]
[TD]Bob sent[/TD]
[TD]11/2/2014[/TD]
[TD]Pete sent[/TD]
[TD]11/7/2014[/TD]
[TD]Pete Sent[/TD]
[TD]11/22/2014[/TD]
[TD]Pete sent[/TD]
[TD]1/15/2015[/TD]
[TD]Pete sent[/TD]
[/TR]
[TR]
[TD]88888[/TD]
[TD]gggg[/TD]
[TD]gggg[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]9/13/2014[/TD]
[TD]Absent[/TD]
[TD]8/28/2014[/TD]
[TD]7/1/2015[/TD]
[TD]10/4/2014[/TD]
[TD]Bob sent[/TD]
[TD]11/2/2014[/TD]
[TD]Pete sent[/TD]
[TD]11/7/2014[/TD]
[TD]Pete Sent[/TD]
[TD]11/22/2014[/TD]
[TD]Pete sent[/TD]
[TD]1/15/2015[/TD]
[TD]Pete sent[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2
Code:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]ID[/TH]
[TH]Last Name[/TH]
[TH]First Name[/TH]
[TH]School[/TH]
[TH]Absences[/TH]
[TH]Current School[/TH]
[/TR]
[TR]
[TD]11111[/TD]
[TD]xxxx[/TD]
[TD]xxxx[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]yyyy[/TD]
[TD]yyyy[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]zzzz[/TD]
[TD]zzzz[/TD]
[TD]C[/TD]
[TD]1[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]44444[/TD]
[TD]aaaa[/TD]
[TD]aaaa[/TD]
[TD]D[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD]dddd[/TD]
[TD]dddd[/TD]
[TD]E[/TD]
[TD]1[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]77777[/TD]
[TD]ffff[/TD]
[TD]ffff[/TD]
[TD]F[/TD]
[TD]1[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]88888[/TD]
[TD]gggg[/TD]
[TD]gggg[/TD]
[TD]G[/TD]
[TD]2[/TD]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2 Final
Code:
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]ID[/TH]
[TH]Last Name[/TH]
[TH]First Name[/TH]
[TH]School[/TH]
[TH]Absences[/TH]
[TH]Current School[/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD]11111[/TD]
[TD]xxxx[/TD]
[TD]xxxx[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]9/14/2014[/TD]
[TD]Jack sent[/TD]
[TD]9/23/2014[/TD]
[TD]Jack sent[/TD]
[TD]11/16/2014[/TD]
[TD]Jill sent[/TD]
[TD]12/9/2014[/TD]
[TD]Mike sent[/TD]
[TD]12/12/2014[/TD]
[TD]Mike sent[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]yyyy[/TD]
[TD]yyyy[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/1/2014[/TD]
[TD]Mark sent[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33333[/TD]
[TD]zzzz[/TD]
[TD]zzzz[/TD]
[TD]C[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/14/2014[/TD]
[TD][/TD]
[TD]11/23/2014[/TD]
[TD][/TD]
[TD]12/16/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44444[/TD]
[TD]aaaa[/TD]
[TD]aaaa[/TD]
[TD]D[/TD]
[TD]1[/TD]
[TD]D[/TD]
[TD]11/2/2014[/TD]
[TD]AL sent[/TD]
[TD]11/16/2014[/TD]
[TD]AL sent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/17/2014[/TD]
[TD]Joe sent[/TD]
[/TR]
[TR]
[TD]55555[/TD]
[TD]dddd[/TD]
[TD]dddd[/TD]
[TD]E[/TD]
[TD]1[/TD]
[TD]E[/TD]
[TD]9/11/2014[/TD]
[TD]Sam sent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/9/2014[/TD]
[TD]Ted sent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]77777[/TD]
[TD]ffff[/TD]
[TD]ffff[/TD]
[TD]F[/TD]
[TD]1[/TD]
[TD]F[/TD]
[TD]1/15/2015[/TD]
[TD]Dave sent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]88888[/TD]
[TD]gggg[/TD]
[TD]gggg[/TD]
[TD]G[/TD]
[TD]2[/TD]
[TD]G[/TD]
[TD]10/4/2014[/TD]
[TD]Bob sent[/TD]
[TD]11/2/2014[/TD]
[TD]Pete sent[/TD]
[TD]11/7/2014[/TD]
[TD]Pete Sent[/TD]
[TD]11/22/2014[/TD]
[TD]Pete sent[/TD]
[TD]1/15/2015[/TD]
[TD]Pete sent[/TD]
[/TR]
</tbody>[/TABLE]