Transposing multiple rows to one column based on criteria

D

Dash2701

New Member
Joined
Mar 24, 2011
Messages
5
I currently have data in the following format:

Recruiter Candidate Requisiton Activity Activity Date
AAA John Doe Banker Applied 1/1/11
AAA John Doe Banker In house review 1/1/11
BBB Jane Doe Admin Applied 1/2/11
BBB Jane Doe Admin In house review 1/3/11
BBB Jane Doe Admin Phone Screen 1/4/11

There are 8 different possible activities: applied, in house review, phone screen, hr interview, 1st interview, 2nd interview, disqualified, hired. We need the data to show one row per candidate per requisition with the activities listed as headers and the activity date under the appropriate header. See below:

Recruiter/Candidate/Requisition/Applied/In house /Phone/HR interview/1st interview/2nd interview/Disqualified/Hired
AAA John Doe Banker 1/1/11 1/1/11
BBB Jane Doe Admin 1/2/11 1/3/11 1/4/11
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Dash2701,

Welcome to the MrExcel forum.


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:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net
 
Upvote 0
I am using Excel 2003

Before:
Excel Workbook
ABCDE
1RecruiterCandidateRequisitionActivityApp Date
2SARJohn SmithHelp Desk SupportApplied3/1/2011
3JACJane Doe***'t ManagerApplied2/22/11
4JACJohn DoeMortgage Origination ManagerApplied3/18/2011
5AFESuzy SmithBilingual Inside SalesApplied2/21/11
6SARMichael MillerHelp Desk SupportApplied2/27/2011
7AFEJoseph DoePT CollectorsDisqualified/Declined3/15/2011
8SARDonald DuckManager Systems Development & OperationsApplied3/3/2011
9JACAllen DavisMortgage Origination ManagerIn House Review3/8/2011
10EMOAaron ShawCollections/Loss Mitigation ManagerApplied3/10/2011
11AFEAlex RodriguezIndirect Lending Sales RepresentativeApplied1/24/11
12AFEJeremy DoeTeller/Customer Service RepApplied1/8/11
13AFEDavid GonzalezBilingual Inside SalesApplied3/3/2011
14JACAnthony RodriguezBranch SupervisorDisqualified/Declined2/27/11
15JACAnthony Rodriguez***'t ManagerApplied3/4/2011
Sheet1
Excel 2003



After:
Excel Workbook
ABCDEFGHIJK
1RecruiterCandidateRequisitionAppliedIn House ReviewPhone ScreenHR Interview1st Interview2nd InterviewDisqualifiedHired
2SARJohn SmithHelp Desk Support3/1/2011
3JACJane Doe***'t Manager2/22/2011
4JACJohn DoeMortgage Origination Manager3/18/2011
5AFESuzy SmithBilingual Inside Sales2/21/2011
6SARMichael MillerHelp Desk Support2/27/2011
7AFEJoseph DoePT Collectors3/15/2011
8SARDonald DuckManager Systems Development & Operations3/3/2011
9JACAllen DavisMortgage Origination Manager3/8/2011
10EMOAaron ShawCollections/Loss Mitigation Manager3/10/2011
11AFEAlex RodriguezIndirect Lending Sales Representative1/24/2011
12AFEJeremy DoeTeller/Customer Service Rep1/8/2011
13AFEDavid GonzalezBilingual Inside Sales3/3/2011
14JACAnthony RodriguezBranch Supervisor2/27/112/27/2011
15JACAnthony Rodriguez***'t Manager3/4/2011
Sheet1
Excel 2003
 
Last edited:
Upvote 0
Dash2701,


Sample raw data before the macro:


Excel Workbook
ABCDEFGHIJKLM
1RecruiterCandidateRequisitionActivityApp Date
2SARJohn SmithHelp Desk SupportApplied3/1/2011
3JACJane Doe***'t ManagerApplied2/22/2011
4JACJohn DoeMortgage Origination ManagerApplied3/18/2011
5AFESuzy SmithBilingual Inside SalesApplied2/21/2011
6SARMichael MillerHelp Desk SupportApplied2/27/2011
7AFEJoseph DoePT CollectorsDisqualified/Declined3/15/2011
8SARDonald DuckManager Systems Development & OperationsApplied3/3/2011
9JACAllen DavisMortgage Origination ManagerIn House Review3/8/2011
10EMOAaron ShawCollections/Loss Mitigation ManagerApplied3/10/2011
11AFEAlex RodriguezIndirect Lending Sales RepresentativeApplied1/24/2011
12AFEJeremy DoeTeller/Customer Service RepApplied1/8/2011
13AFEDavid GonzalezBilingual Inside SalesApplied3/3/2011
14JACAnthony RodriguezBranch SupervisorDisqualified/Declined2/27/2011
15JACAnthony Rodriguez***'t ManagerApplied3/4/2011
16
Sheet1





After the macro:


Excel Workbook
ABCDEFGHIJK
1RecruiterCandidateRequisitionAppliedIn House ReviewPhone ScreenHR Interview1 st Interview2 nd InterviewDisqualifiedHired
2SARJohn SmithHelp Desk Support3/1/2011
3JACJane Doe***'t Manager2/22/2011
4JACJohn DoeMortgage Origination Manager3/18/2011
5AFESuzy SmithBilingual Inside Sales2/21/2011
6SARMichael MillerHelp Desk Support2/27/2011
7AFEJoseph DoePT Collectors3/15/2011
8SARDonald DuckManager Systems Development & Operations3/3/2011
9JACAllen DavisMortgage Origination Manager3/8/2011
10EMOAaron ShawCollections/Loss Mitigation Manager3/10/2011
11AFEAlex RodriguezIndirect Lending Sales Representative1/24/2011
12AFEJeremy DoeTeller/Customer Service Rep1/8/2011
13AFEDavid GonzalezBilingual Inside Sales3/3/2011
14JACAnthony RodriguezBranch Supervisor2/27/2011
15JACAnthony Rodriguez***'t Manager3/4/2011
16
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, 04/07/2011
' http://www.mrexcel.com/forum/showthread.php?t=541680
Dim c As Range
Application.ScreenUpdating = False
Range("F1:M1") = [{"Applied","In House Review","Phone Screen","HR Interview","1 st Interview","2 nd Interview","Disqualified","Hired"}]
For Each c In Range("D2", Range("D" & Rows.Count).End(xlUp))
  Select Case c.Value
    Case "Applied"
      c.Offset(, 1).Copy Cells(c.Row, "F")
    Case "In House Review"
      c.Offset(, 1).Copy Cells(c.Row, "G")
    Case "Phone Screen"
      c.Offset(, 1).Copy Cells(c.Row, "H")
    Case "HR Interview"
      c.Offset(, 1).Copy Cells(c.Row, "I")
    Case "1 st Interview"
      c.Offset(, 1).Copy Cells(c.Row, "J")
    Case "2 nd Interview"
      c.Offset(, 1).Copy Cells(c.Row, "K")
    Case "Disqualified/Declined"
      c.Offset(, 1).Copy Cells(c.Row, "L")
    Case "Hired"
      c.Offset(, 1).Copy Cells(c.Row, "M")
  End Select
Next c
Columns("D:E").Delete
Columns("D:K").AutoFit
Application.ScreenUpdating = True
End Sub


Then run the ReorgData macro.


You may want to consider having the macro results in a new worksheet Results.
 
Upvote 0
It seems to still be listing each activity on a separate row for the same candidate/job combination. I need to have one row per candidate per job.
Excel Workbook
ABCDEFGHIJ
1RecruiterCandidateRequisitionAppliedIn House ReviewPhone ScreenHR Interview1 st Interview2 nd InterviewDisqualified
2JACALoss Prevention Specialist - [37-66]
3EMOBCall Center Associates - [30-175]4/6/11
4KMSCManager of Employment and Employee Relations - [36-19]4/6/11
5KMSCManager of Employment and Employee Relations - [36-19]4/6/11
6KMSCManager of Employment and Employee Relations - [36-19]4/6/11
7KMSDManager of Employment and Employee Relations - [36-19]4/6/11
8KMSDManager of Employment and Employee Relations - [36-19]4/6/11
9JACETeller Customer Service/Port Orange E - Part Time - [44-22]4/6/11
10JACFLoss Prevention Specialist - [37-66]4/6/11
11KMSGManager of Employment and Employee Relations - [36-19]4/6/11
12KMSHManager of Employment and Employee Relations - [36-19]4/6/11
13KMSHManager of Employment and Employee Relations - [36-19]4/6/11
14KMSIManager of Employment and Employee Relations - [36-19]4/6/11
15KMSIManager of Employment and Employee Relations - [36-19]4/6/11
16KMSJManager of Employment and Employee Relations - [36-19]4/6/11
17KMSJManager of Employment and Employee Relations - [36-19]4/6/11
18JACK***'t Manager/Port Malabar - [22-13]4/6/11
19KMSLManager of Employment and Employee Relations - [36-19]4/6/11
20KMSLManager of Employment and Employee Relations - [36-19]4/6/11
21KMSMManager of Employment and Employee Relations - [36-19]4/6/11
22KMSMManager of Employment and Employee Relations - [36-19]4/6/11
23KMSMManager of Employment and Employee Relations - [36-19]4/6/11
24KMSNManager of Employment and Employee Relations - [36-19]4/6/11
25KMSNManager of Employment and Employee Relations - [36-19]4/6/11
26KMSOManager of Employment and Employee Relations - [36-19]4/6/11
27KMSOManager of Employment and Employee Relations - [36-19]4/6/11
Report
Excel 2003

I really appreciate all of your help on this.
 
Upvote 0
Dash2701,

The macro returned your requirements per your first screenshots. Your post # 3.


Can I see the raw data for your last screenshot, post #5.
 
Upvote 0
Sorry, it was probably a bad sample of data to use. Here is the raw data from the last screen shot. They won't always have the same activity date.

Excel Workbook
ABCDE
1RecruiterCandidateRequisitionActivityActivity Date
2JACALoss Prevention Specialist - [37-66]1st Interview4/6/11
3EMOBCall Center Associates - [30-175]HR Interview4/6/11
4KMSCManager of Employment and Employee Relations - [36-19]Applied4/6/11
5KMSCManager of Employment and Employee Relations - [36-19]In House Review4/6/11
6KMSCManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
7KMSDManager of Employment and Employee Relations - [36-19]In House Review4/6/11
8KMSDManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
9JACETeller Customer Service/Port Orange E - Part Time - [44-22]In House Review4/6/11
10JACFLoss Prevention Specialist - [37-66]In House Review4/6/11
11KMSGManager of Employment and Employee Relations - [36-19]In House Review4/6/11
12KMSHManager of Employment and Employee Relations - [36-19]In House Review4/6/11
13KMSHManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
14KMSIManager of Employment and Employee Relations - [36-19]In House Review4/6/11
15KMSIManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
16KMSJManager of Employment and Employee Relations - [36-19]In House Review4/6/11
17KMSJManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
18JACK***'t Manager/Port Malabar - [22-13]Disqualified/Declined4/6/11
19KMSLManager of Employment and Employee Relations - [36-19]In House Review4/6/11
20KMSLManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
21KMSMManager of Employment and Employee Relations - [36-19]In House Review4/6/11
22KMSMManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
23KMSNManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
24KMSOManager of Employment and Employee Relations - [36-19]In House Review4/6/11
25KMSOManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/11
Report
Excel 2003
 
Upvote 0
Dash2701,


Sample raw data in worksheet Report:


Excel Workbook
ABCDE
1RecruiterCandidateRequisitionActivityActivity Date
2JACALoss Prevention Specialist - [37-66]1st Interview4/6/2011
3EMOBCall Center Associates - [30-175]HR Interview4/6/2011
4KMSCManager of Employment and Employee Relations - [36-19]Applied4/6/2011
5KMSCManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
6KMSCManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
7KMSDManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
8KMSDManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
9JACETeller Customer Service/Port Orange E - Part Time - [44-22]In House Review4/6/2011
10JACFLoss Prevention Specialist - [37-66]In House Review4/6/2011
11KMSGManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
12KMSHManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
13KMSHManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
14KMSIManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
15KMSIManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
16KMSJManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
17KMSJManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
18JACK***'t Manager/Port Malabar - [22-13]Disqualified/Declined4/6/2011
19KMSLManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
20KMSLManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
21KMSMManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
22KMSMManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
23KMSNManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
24KMSOManager of Employment and Employee Relations - [36-19]In House Review4/6/2011
25KMSOManager of Employment and Employee Relations - [36-19]Disqualified/Declined4/6/2011
26
Report





After the macro in a new worksheet Results. I had to change the titles in row 1 that are bold to match the items in worksheert Report column Activity.:


Excel Workbook
ABCDEFGHIJK
1RecruiterCandidateRequisitionAppliedIn House ReviewPhone ScreenHR Interview1st Interview2nd InterviewDisqualified/DeclinedHired
2JACALoss Prevention Specialist - [37-66]4/6/2011
3EMOBCall Center Associates - [30-175]4/6/2011
4KMSCManager of Employment and Employee Relations - [36-19]4/6/20114/6/20114/6/2011
5KMSDManager of Employment and Employee Relations - [36-19]4/6/20114/6/2011
6JACETeller Customer Service/Port Orange E - Part Time - [44-22]4/6/2011
7JACFLoss Prevention Specialist - [37-66]4/6/2011
8KMSGManager of Employment and Employee Relations - [36-19]4/6/2011
9KMSHManager of Employment and Employee Relations - [36-19]4/6/20114/6/2011
10KMSIManager of Employment and Employee Relations - [36-19]4/6/20114/6/2011
11KMSJManager of Employment and Employee Relations - [36-19]4/6/20114/6/2011
12JACK***'t Manager/Port Malabar - [22-13]4/6/2011
13KMSLManager of Employment and Employee Relations - [36-19]4/6/20114/6/2011
14KMSMManager of Employment and Employee Relations - [36-19]4/6/20114/6/2011
15KMSNManager of Employment and Employee Relations - [36-19]4/6/2011
16KMSOManager of Employment and Employee Relations - [36-19]4/6/20114/6/2011
17
Results





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 ReorgDataV2()
' hiker95, 04/08/2011
' http://www.mrexcel.com/forum/showthread.php?t=541680
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, LR2 As Long, a As Long, aa As Long, SR As Long, ER As Long, FC As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Report")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
With w1.Range("G2")
  .Formula = "=A2&B2&C2"
  .AutoFill Destination:=w1.Range("G2:G" & LR)
End With
w1.Range("A1:C" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Range("A1"), Unique:=True
wR.Range("G1:N1") = [{"Applied","In House Review","Phone Screen","HR Interview","1st Interview","2nd Interview","Disqualified/Declined","Hired"}]
LR2 = wR.Cells(Rows.Count, 1).End(xlUp).Row
With wR.Range("D2")
  .Formula = "=A2&B2&C2"
  .AutoFill Destination:=wR.Range("D2:D" & LR2)
End With
With wR.Range("E2")
  .Formula = "=MATCH(D2,Report!G:G,0)"
  .AutoFill Destination:=wR.Range("E2:E" & LR2)
End With
With wR.Range("F2")
  .Formula = "=E3-1"
  .AutoFill Destination:=wR.Range("F2:F" & LR2 - 1)
End With
wR.Range("F" & LR2) = LR
For a = 2 To LR2 Step 1
  SR = wR.Range("E" & a).Value
  ER = wR.Range("F" & a).Value
  For aa = SR To ER Step 1
    FC = Application.Match(w1.Range("D" & aa), wR.Rows(1), 0)
    w1.Range("E" & aa).Copy wR.Cells(a, FC)
  Next aa
Next a
wR.Columns("D:F").Delete
w1.Columns("G").Delete
wR.Columns("A:K").AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub


Then run the ReorgDataV2 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Index min value from one column based on multiple matches in another column
Replies
2
Views
455
Anonymous1378
A
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
124
Fluff
Fluff
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
543
sspatriots
S
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
606
Greenbehindthecells
G

Forum statistics

Threads
1,224,581
Messages
6,179,668
Members
452,936
Latest member
anamikabhargaw

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