Solved

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

Posted on 2011-03-18
5
179 Views
Last Modified: 2012-05-11
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
Comment
Question by:JoshFink
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35168178
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
 

Author Comment

by:JoshFink
ID: 35168191
Your logic sounds right but no clue about macros/vba.. I'll have to do some reading.

Thanks
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 35168290
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35168301
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
 

Author Closing Comment

by:JoshFink
ID: 35168449
Nice.. This works great.. Thanks so much
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question