Get Info on One Line (Excel 97 SR2)

Topic

New Reply

I am trying to use formulas to extract information from a table in the attached spreadsheet. I need to get the activity, cost center and amount on one line in separate cells. The actual report has a lot more info than I am showing here.

I could just continue what I’m doing and then sort the columns but I will need to do this every month when we are in a rush.

Ideally, I would write a macro that would extract the information to a new location or a new tab, but I haven

Reply | Quote

Replies

I had a quick look.

My attachment has a macro which may do the trick.
Shortcut key assigned – Press [Ctrl][Shift][Z]

zeddy

Reply | Quote

Here is another macro. It assumes the”input” is on sheet1 and will send the output to sheet2. Change as appropriate

Steve

Option Explicit

Sub SobersheExtract()
    Dim shtIn As Worksheet
    Dim shtOut As Worksheet
    Dim rCell As Range
    Dim rng As Range
    Dim x As Integer
    
    Set shtIn = Worksheets("sheet1")
    Set shtOut = Worksheets("sheet2")
    shtIn.Select
    Set rng = shtIn.Range(Range("a1"), Range("A65536").End(xlUp))
    
    shtOut.Cells.ClearContents
    
    shtOut.Cells(1, 1).Value = "Activity"
    shtOut.Cells(1, 2).Value = "Cost Center"
    shtOut.Cells(1, 3).Value = "Amount"
    
    x = 2
    For Each rCell In rng
        If Left(rCell.Value, 10) = "DEPARTMENT" Then
            shtOut.Cells(x, 1).Value = Right(rCell.Value, 3)
            shtOut.Cells(x, 2).Value = Mid(rCell.Value, 14, 5)
            shtOut.Cells(x, 3).Value = rCell.Offset(2, 7).Value
            x = x + 1
        End If
    Next rCell
End Sub

Reply | Quote

This macro is great too! Using this one I don’t have to do any formulas. Awesome.

Thank You!

PS. The Lounge always comes through!!!!!!

Reply | Quote

It works great. Thank You. I never used Advanced Filter before so can you explain what the following portion of your macro is doing?

Sheets(zSource).Select
[h4] = “xx” …………………. I understand this is making a header row for the data filter
[L4] = “zz”
Sheets(zNew).Select
Set zData = Sheets(zSource).[h4].CurrentRegion ………….. Here you are selecting the “current region” of the header
zData.AdvancedFilter Action:=xlFilterCopy, _ …………….. The next 3 lines are the part I don’t understand
CriteriaRange:=Range(“A1:C2”), _
CopyToRange:=Range(“A4:C4”), Unique:=False

Thanks Again!!

Reply | Quote