Solved

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

Posted on 2011-03-18
5
178 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
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 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