• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Displaying a value based on a row of data on another sheet

Let me try and explain what I'm trying to do.

I have a sheet that has the following data

A                         B
05/16/2011       Josh
05/17/2011       Drew
05/18/2011       Dave
05/19/2011       Josh
05/20/2011       Josh
05/21/2011       Drew

This goes on for 83 rows

On another sheet I need to create a report sheet that lists all the data from the previous sheet but I need it listed by Person

A                        C                             E

Josh                  Drew                       Dave
05/16/2011        05/17/2011              05/18/2011
05/19/2011        05/21/2011
05/20/2011        


Unfortunately, I have no idea how to accomplish this or even where to look. Does this make sense?

Thanks

Josh
0
JoshFink
Asked:
JoshFink
  • 3
  • 2
1 Solution
 
rspahitzCommented:
This sounds like a job for VBA.  Are you familiar with that/macros?

Here's the way I would handle it (VBA code forthcoming):

For each row in column B of sheet 1, proceed to sheet 2 and look across row 1 for the item until you find it or a blank.  If not found, add to the end.  Either way, grab column A of sheet 1 and copy its value into the first blank row under the column where you found/added the name.
0
 
JoshFinkAuthor Commented:
Your logic sounds right but no clue about macros/vba.. I'll have to do some reading.

Thanks
0
 
rspahitzCommented:
From the sheet with the two columns (dates, names), press Alt-F11 and you should end up in a new app entitled Microsoft Visual Basic for Applications.

you should see a blank window inside the app entitled something like "MyWorkbook - Sheet1 (Code)" (It may also say "Option Explicit" in the window.

Put the following code into that window, and update all references to "Sheet2" with whatever you named the destination sheet:

 
Sub SummarizeByUser()
    Dim objCell As Range
    Dim iRow As Integer
    Dim strName As String
    Dim iSheet2Column As Integer
    Dim iSheet2Row As Integer
    
    iRow = 1
    Do
        strName = Sheets("Sheet1").Cells(iRow, 2).Value
        If strName = "" Then
            Exit Do
        End If
        
        iSheet2Column = GetColumn(strName)
        iSheet2Row = GetBlankRow(iSheet2Column)
        Sheets("Sheet2").Cells(iSheet2Row, iSheet2Column).Value = Sheets("Sheet1").Cells(iRow, 1).Value
        iRow = iRow + 1
    Loop
End Sub

Private Function GetColumn(UserName As String) As Integer
    Dim iColumn As Integer
    Dim strName As String
    
    iColumn = 1
    Do
        strName = Sheets("Sheet2").Cells(1, iColumn).Value
        If strName = UserName Or strName = "" Then
            If strName = "" Then
                Sheets("Sheet2").Cells(1, iColumn).Value = UserName
            End If
            Exit Do
        End If
        iColumn = iColumn + 1
    Loop
    
    GetColumn = iColumn
End Function

Private Function GetBlankRow(ColumnNumber As Integer) As Integer
    Dim iRow As Integer
    
    iRow = 1
    Do Until Sheets("Sheet2").Cells(iRow, ColumnNumber).Value = ""
        iRow = iRow + 1
    Loop
    GetBlankRow = iRow
End Function

Open in new window

0
 
rspahitzCommented:
With that in place, click somewhere between Sub SummarizeByUser and the first End Sub, and press the F5 key to run it.

You should then have the information in Sheet2.
0
 
JoshFinkAuthor Commented:
Nice.. This works great.. Thanks so much
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now