Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Orlando, Florida, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consolidating Text information (2003)

    I have a workbook that contains 3 worksheets that are completed by 3 different people. The column headings are the same. I need to have a 4th sheet that contains the information from the other three sheets and if there are any rows that have the same information first row they need to be consolidated together into one row. Well, my thought was that I would use the consolidate function. So I put some test date in the three sheets and consolidate worked great as long as my first column was text and the others were numeric. My problem lies in the fact that most of the data in the spreadsheet is text. Is there a way to consolidate unique text data?

    I have attached a sample with a macro for both numeric and text.

    Any help would be appreciated!

    Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Text information (2003)

    I would theink the easiest way would be to loop thru the sheets and copy everything from row 2 to the end of each sheet onto a temp sheet then use adv filter to get the unique items. Some code would be like:

    <pre>Option Explicit
    Sub MyConsolidate()
    Dim wksNew As Worksheet
    Dim wksTemp As Worksheet
    Dim rng As Range
    Dim i As Integer
    Dim iWks As Integer

    iWks = Worksheets.Count

    Set wksTemp = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    Set wksNew = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    wksNew.Name = "Consolidated"
    Worksheets(1).Rows(1).Copy wksTemp.Rows(1)

    For i = 2 To iWks
    With Worksheets(i)
    Set rng = .Range(.Range("IV1").End(xlToLeft).Offset(1, 0), _
    .Range("a65536").End(xlUp))
    End With
    rng.Copy wksTemp.Range("a65536").End(xlUp).Offset(1, 0)
    Next
    With wksTemp
    .UsedRange.AdvancedFilter _
    Action:=xlFilterInPlace, Unique:=True
    .Cells.SpecialCells(xlCellTypeVisible).Copy wksNew.Cells(1)
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
    End With
    Set rng = Nothing
    Set wksTemp = Nothing
    Set wksNew = Nothing
    End Sub</pre>



    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Orlando, Florida, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Text information (2003)

    This is working however it is only pulling data from 2 of the three sheets in the workbook. Could I have something set up wrong?

    Thanks for your help!
    Jill

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Orlando, Florida, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Text information (2003)

    I just played around with this a bit more and found that if I add a sheet (in my case I added it at the beginning of the workbook) that just contains the headers and then I have three worksheets that contain data it works great! Thanks!

    Is there a way for it to use the headers from the worksheets, which are all the same, without having to have the header sheet?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Text information (2003)

    OOPS <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I had played with code copying sheets and decided on createing new sheets. I only copy the header from sheet1 but not the data.

    Change:
    <pre> For i = <font color=red>2</font color=red> To iWks</pre>


    To
    <pre> For i = <font color=red>1</font color=red> To iWks</pre>


    Sorry,
    Steve

  6. #6
    New Lounger
    Join Date
    Feb 2003
    Location
    Orlando, Florida, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Text information (2003)

    No need to be sorry! You are my hero!

    Thanks a lot.
    Jill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •