VLOOKUP or VBA to extract data from multiple sheets

[Vijaychitra]

Team,

Need your help & Guidance to achieve the desired result using VB MACRO

Attached the spreadsheet

Raw Data Sheet - have 5 columns
Report sheet - Desired sheet in the format as required.

1. How to traverse the column from E-> C in raw data sheet for the emp #.
2. Find the first mail ID column data & copy to the First Escalation column Mail
3. On the next iteration , next data column need to be copied into Second Escalation Mail column

Let me know if you can use VLOOKUP or another option to get the desired result.

 

[p45cal]

Sub blah()
Set Destn = Sheets("Report").Range("A1")
Set SceRng = Sheets("Raw Data").Range("A1").CurrentRegion.Resize(, 5)
With SceRng
  Union(.Columns(1), .Columns(5)).Copy Destn
  .Columns(4).Copy Destn.Offset(, 2)
  .Columns(3).Copy Destn.Offset(, 3)
  Destn.Resize(.Rows.Count, 4).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
  Destn.Resize(.Rows.Count, 4).Columns(4).Clear
End With
Destn.Resize(, 3).Value = Array("Employee #", "First Escalation Mail", "Secondary Escalation Mail")
End Sub

?

 

[Vijaychitra]

Source Data

Emp #	Employee Name	Escalation Level 4 Email	Escalation Level 3 Email	Escalation Level 2 Email	Escalation Level 1 Email
1489	Alex	Alexesclation4test.com		Alexesclation2test.com
1655	Joe		Joeesclation3test.com
1748	Sam
1586	Peter		Peteresclation3@test.com	Peteresclation2@test.com
1789	Linda	Lindaesclation4@test.com	Lindaesclation3@test.com		Lindaesclation1@test.com

I need to get a report table using VBA which will fetch the first 2 occurrence of email ID for each Emp # - Traverse from right to Left in above table to get the first 2 email occurrences

Desired out put

Emp #	Emp Name	First Mail	Second Mail
1489	Alex	Alexesclation2test.com	Alexesclation4test.com
1655	Joe	Joeesclation3test.com
1748	Sam	Samesclation1@test.com
1586	Peter	Peteresclation2@test.com	Peteresclation3@test.com
1789	Linda	Lindaesclation1@test.com	Lindaesclation3@test.com

Thanks for the help...

 

[Vijaychitra]

Sub blah()
Set Destn = Sheets("Report").Range("A1")
Set SceRng = Sheets("Raw Data").Range("A1").CurrentRegion.Resize(, 5)
With SceRng
  Union(.Columns(1), .Columns(5)).Copy Destn
  .Columns(4).Copy Destn.Offset(, 2)
  .Columns(3).Copy Destn.Offset(, 3)
  Destn.Resize(.Rows.Count, 4).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
  Destn.Resize(.Rows.Count, 4).Columns(4).Clear
End With
Destn.Resize(, 3).Value = Array("Employee #", "First Escalation Mail", "Secondary Escalation Mail")
End Sub

?

Thanks...

However the data source is as mentioned below

Source Data

Emp #	Employee Name	Escalation Level 4 Email	Escalation Level 3 Email	Escalation Level 2 Email	Escalation Level 1 Email
1489	Alex	Alexesclation4test.com		Alexesclation2test.com
1655	Joe		Joeesclation3test.com
1748	Sam
1586	Peter		Peteresclation3@test.com	Peteresclation2@test.com
1789	Linda	Lindaesclation4@test.com	Lindaesclation3@test.com		Lindaesclation1@test.com

I need to get a report table using VBA which will fetch the first 2 occurrence of email ID for each Emp # - Traverse from right to Left in above table to get the first 2 email occurrences

Desired out put

Emp #	Emp Name	First Mail	Second Mail
1489	Alex	Alexesclation2test.com	Alexesclation4test.com
1655	Joe	Joeesclation3test.com
1748	Sam	Samesclation1@test.com
1586	Peter	Peteresclation2@test.com	Peteresclation3@test.com
1789	Linda	Lindaesclation1@test.com	Lindaesclation3@test.com

Thanks for the help...

 

[Vijaychitra]

Need VBA help

 

[p45cal]

Sub blah()
Set Destn = Sheets("Report").Range("A1")
Set scerng = Sheets("Raw Data").Range("A1").CurrentRegion.Resize(, 6)
With scerng
  Union(.Columns(1), .Columns(2), .Columns(6)).Copy Destn
  .Columns(5).Copy Destn.Offset(, 3)
  .Columns(4).Copy Destn.Offset(, 4)
  .Columns(3).Copy Destn.Offset(, 5)
  Destn.Resize(.Rows.Count, 5).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
  Destn.Offset(, 4).Resize(.Rows.Count, 2).Clear
End With
Destn.Resize(, 4).Value = Array("Emp#", "Emp Nname", "First Mail", "Second Mail")
End Sub