Large spreadsheet going from column to rows

N

ngold1968

New Member
Joined
Nov 10, 2010
Messages
34
Good morning everyone.
I have a large spreadsheet with data pulled from my CDW that I need help with.

columns are; (a) Assignment #, (b) parent name, (c) co. name, (d) step, (e) step date time, (f) status

each assignment # has multiple rows indicating step (open, closed, exception) along with a date time.
Also there are multiple parent and co names.

What I would like to do is take the rows based on assignment # and merge all into one row.

Can this be done?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
ngold1968,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:
 
Upvote 0
I am using Excel 2007.

Below is what the raw data looks like and below is what I would like it to do.

<TABLE style="WIDTH: 758pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1010 border=0><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4 1pt solid; WIDTH: 55pt; BORDER-BOTTOM: #608bb4 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #bfd2e2" width=73 height=18>Assignment #</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; WIDTH: 87pt; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2" width=116>Parent</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; WIDTH: 70pt; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2" width=93>CO</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; WIDTH: 80pt; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2" width=107>Step</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; WIDTH: 98pt; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2" width=130>Status</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; WIDTH: 104pt; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2" width=139>Step Date time</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 98pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=130></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=83></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 104pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=139></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>1</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Closeout</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">CLOSED</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:15:19 PM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>1</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Received by Citizens</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">IN_PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:13:19 PM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>1</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Assigned to IPA</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">IN_PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Aug 12, 2010 12:00:00 AM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>2</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Cancelled Inspection</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">CLOSED</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Feb 18, 2011 2:00:00 AM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>2</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Assigned to IPA</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">IN_PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Sep 24, 2010 12:00:00 AM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>2</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Cancelled Inspection</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">EXCEPTION</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Feb 18, 2011 12:00:00 AM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>3</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Closeout</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">CLOSED</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:15:19 PM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>3</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Received by Citizens</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">IN_PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:13:19 PM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>3</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Assigned to IPA</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">IN_PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Sep 24, 2010 12:00:00 AM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>4</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Closeout</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">CLOSED</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:15:20 PM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>4</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Received by Citizens</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">IN_PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:13:20 PM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>4</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Assigned to IPA</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">IN_PROCESS</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Sep 24, 2010 12:00:00 AM</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4 1pt solid; BORDER-BOTTOM: #608bb4 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #bfd2e2" height=18>Assignment #</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">Parent</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">CO</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">Step</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">Step Date time</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">Step</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">Step Date time</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">Step</TD><TD class=xl63 style="BORDER-RIGHT: #608bb4 1pt solid; BORDER-TOP: #608bb4 1pt solid; BORDER-LEFT: #608bb4; BORDER-BOTTOM: #608bb4 1pt solid; BACKGROUND-COLOR: #bfd2e2">Step Date time</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc 1pt solid; BORDER-BOTTOM: #cccccc 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>1</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Depot, Inc.</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Unknown Member</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Inspection Closeout</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:15:19 PM</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Received by Citizens</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Apr 27, 2011 7:13:19 PM</TD><TD class=xl65 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent">Assigned to IPA</TD><TD class=xl66 style="BORDER-RIGHT: #cccccc 1pt solid; BORDER-TOP: #cccccc 1pt solid; BORDER-LEFT: #cccccc; BORDER-BOTTOM: #cccccc 1pt solid; BACKGROUND-COLOR: transparent" align=right>Aug 12, 2010 12:00:00 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
 
Upvote 0
ngold1968,


I assume that your raw data is already soreted/grouped per your latest screenshot.


Sample raw data:


Excel Workbook
ABCDEF
1Assignment #ParentCOStepStatusStep Date time
21Inspection Depot, Inc.Unknown MemberInspection CloseoutCLOSED4/27/2011 19:15
31Inspection Depot, Inc.Unknown MemberReceived by CitizensIN_PROCESS4/27/2011 19:13
41Inspection Depot, Inc.Unknown MemberAssigned to IPAIN_PROCESS8/12/2010 0:00
52Inspection Depot, Inc.Unknown MemberCancelled InspectionCLOSED2/18/2011 2:00
62Inspection Depot, Inc.Unknown MemberAssigned to IPAIN_PROCESS9/24/2010 0:00
72Inspection Depot, Inc.Unknown MemberCancelled InspectionEXCEPTION2/18/2011 0:00
83Inspection Depot, Inc.Unknown MemberInspection CloseoutCLOSED4/27/2011 19:15
93Inspection Depot, Inc.Unknown MemberReceived by CitizensIN_PROCESS4/27/2011 19:13
103Inspection Depot, Inc.Unknown MemberAssigned to IPAIN_PROCESS9/24/2010 0:00
114Inspection Depot, Inc.Unknown MemberInspection CloseoutCLOSED4/27/2011 19:15
124Inspection Depot, Inc.Unknown MemberReceived by CitizensIN_PROCESS4/27/2011 19:13
134Inspection Depot, Inc.Unknown MemberAssigned to IPAIN_PROCESS9/24/2010 0:00
14
Sheet1





After the macro, in the same worksheet (column F displayed as a reference to the raw data):


Excel Workbook
FGHIJKLMNOPQ
1Step Date timeAssignment #ParentCOStepStep Date timeStepStep Date timeStepStep Date time
24/27/2011 19:151Inspection Depot, Inc.Unknown MemberInspection Closeout4/27/2011 19:15Received by Citizens4/27/2011 19:13Assigned to IPA8/12/2010 0:00
34/27/2011 19:132Inspection Depot, Inc.Unknown MemberAssigned to IPA9/24/2010 0:00
48/12/2010 0:003Inspection Depot, Inc.Unknown MemberInspection Closeout4/27/2011 19:15Received by Citizens4/27/2011 19:13Assigned to IPA9/24/2010 0:00
52/18/2011 2:004Inspection Depot, Inc.Unknown MemberInspection Closeout4/27/2011 19:15Received by Citizens4/27/2011 19:13Assigned to IPA9/24/2010 0:00
69/24/2010 0:00
72/18/2011 0:00
84/27/2011 19:15
94/27/2011 19:13
109/24/2010 0:00
114/27/2011 19:15
124/27/2011 19:13
139/24/2010 0:00
14
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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 07/12/2011
' http://www.mrexcel.com/forum/showthread.php?t=563666
Dim LR As Long, a As Long, aa As Long, SR As Long, ER As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
With Range("G2:G" & LR)
  .FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]"
  .Value = .Value
End With
Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I1"), Unique:=True
LR = Cells(Rows.Count, 9).End(xlUp).Row
With Range("L2:L" & LR)
  .FormulaR1C1 = "=RC[-3]&RC[-2]&RC[-1]"
  .Value = .Value
End With
With Range("M1:R1")
  .Value = [{"Step","Step Date time","Step","Step Date time","Step","Step Date time"}]
  .Interior.Color = 14865344
End With
For a = 2 To LR Step 1
  SR = Application.Match(Cells(a, 12), Columns(7), 0)
  ER = Application.Match(Cells(a, 12), Columns(7), 1)
  For aa = SR To ER Step 1
    If InStr(Range("D" & aa), "Inspection Closeout") > 0 Then
      Range("M" & a).Value = "Inspection Closeout"
      Range("N" & a).Value = Range("F" & aa).Value
    ElseIf InStr(Range("D" & aa), "Received by Citizens") > 0 Then
      Range("O" & a).Value = "Received by Citizens"
      Range("P" & a).Value = Range("F" & aa).Value
    ElseIf InStr(Range("D" & aa), "Assigned to IPA") > 0 Then
      Range("Q" & a).Value = "Assigned to IPA"
      Range("R" & a).Value = Range("F" & aa).Value
    End If
  Next aa
Next a
Columns(7).ClearContents
Columns(12).Delete
Range("M2:M" & LR).NumberFormat = "m/d/yyyy h:mm"
Range("O2:O" & LR).NumberFormat = "m/d/yyyy h:mm"
Range("Q2:Q" & LR).NumberFormat = "m/d/yyyy h:mm"
Columns("I:Q").AutoFit
Application.ScreenUpdating = True
End Sub


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 ReorgData macro.


The macro can be re-written to display the results in a new worksheet Results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Send Email from Excel - Launch User's Default Email Client
Replies
3
Views
480
Micron
M
A
  • Question Question
Assign Rows of Data to Employees from Master
Replies
9
Views
532
mumps
mumps
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
482
Anthony47
A
W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
268
Welshy1491
W
U
  • Solved
INDEX Function with Dynamic Array
2
Replies
12
Views
666
UnitedCloud01
U

Forum statistics

Threads
1,224,542
Messages
6,179,421
Members
452,913
Latest member
JWD210

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top