• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP or VBA to extract data from multiple sheets

Vijaychitra

New Member
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.
 

Attachments

  • Excel_Help.xlsx
    11.2 KB · Views: 10
Code:
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
?
 
Source Data

Emp #Employee NameEscalation Level 4 EmailEscalation Level 3 EmailEscalation Level 2 EmailEscalation Level 1 Email
1489AlexAlexesclation4test.comAlexesclation2test.com
1655JoeJoeesclation3test.com
1748Sam
1586PeterPeteresclation3@test.comPeteresclation2@test.com
1789LindaLindaesclation4@test.comLindaesclation3@test.comLindaesclation1@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 NameFirst MailSecond Mail
1489AlexAlexesclation2test.comAlexesclation4test.com
1655JoeJoeesclation3test.com
1748SamSamesclation1@test.com
1586PeterPeteresclation2@test.comPeteresclation3@test.com
1789LindaLindaesclation1@test.comLindaesclation3@test.com

Thanks for the help...
 
Code:
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 NameEscalation Level 4 EmailEscalation Level 3 EmailEscalation Level 2 EmailEscalation Level 1 Email
1489AlexAlexesclation4test.comAlexesclation2test.com
1655JoeJoeesclation3test.com
1748Sam
1586PeterPeteresclation3@test.comPeteresclation2@test.com
1789LindaLindaesclation4@test.comLindaesclation3@test.comLindaesclation1@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 NameFirst MailSecond Mail
1489AlexAlexesclation2test.comAlexesclation4test.com
1655JoeJoeesclation3test.com
1748SamSamesclation1@test.com
1586PeterPeteresclation2@test.comPeteresclation3@test.com
1789LindaLindaesclation1@test.comLindaesclation3@test.com

Thanks for the help...
 
Code:
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
 
Back
Top