Solved

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

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now