CODEWITHSUNDEEP
excelexcel-formula
Liquidgenius
Liquidgenius

Reputation: 708

Find Contents of Another Cell Based on First Specific Text String Above Current Cell

I am trying to come up with a formula that can be run in a different worksheet and references the data below.

Essentially for each cell in D, I want to check upwards and when I find the string "Invoice" I select the next row above and column B. Essentially I'd be finding the company name each time. As you can see, the number of rows of records for each company may vary.

More simply, the formula could just work on column B and return the string directly above the first occurrence of the string "Status".

I dislike how complicated this is, but I have no control over the data format I am receiving and the source refuses to change it.

Original Data

    A               B               C               D
1                   ABC Widgets, Inc.
2   Account         Status          Date            Invoice
3   1423            Open            4/25/2011       123748
4   1423            Closed          5/1/2011        432741
5   1423            Open            5/2/2011        522211
6 
7                   XYZ Sprockets, Inc.
8   Account         Status          Date            Invoice
9   3222            Open            5/3/2011        529999
10

Finished Data

    A               B               C               D            E
2   Account         Status          Date            Invoice      Client
3   1423            Open            4/25/2011       123748       ABC Widgets, Inc.
4   1423            Closed          5/1/2011        432741       ABC Widgets, Inc.
5   1423            Open            5/2/2011        522211       ABC Widgets, Inc.
9   3222            Open            5/3/2011        529999       XYZ Sprockets, Inc.

Nutch,

While I can't get your VBA code to work properly (see comments) it did allow me to come up with this code that finds the first occurrence of "Status" and then offsets 1 cell above. This is essentially what I am trying for but in Formula format.

Sub findFirstStringAbove()
'
Cells.Find(What:="Status", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase _
    :=False).Offset(-1, 0).Select
End Sub

Upvotes: 0

Views: 4874

Answers (4)

Robert Co
Robert Co

Reputation: 1715

OK. Here's the VBA.

Public Sub test()
Dim rngStCol As Range
Dim rngSt As Range
Dim lastRow As Integer
Dim rngDB As Range
Dim currRow As Integer

Set rngStCol = Sheet1.UsedRange.Columns(2)
Set rngSt = rngStCol.Find("Status")
Do While rngSt.Row > lastRow
    Set rngDB = rngSt.CurrentRegion
    Set rngDB = rngDB.Offset(2, 0).Resize(rngDB.Rows.Count - 2)

    If currRow = 0 Then
        Sheet3.Range(Sheet3.Cells(1, 1), Sheet3.Cells(1, rngDB.Columns.Count)).Value = rngDB.Offset(-1, 0).Resize(1).Value
        Sheet3.Cells(1, rngDB.Columns.Count + 1).Value = "Client"
        currRow = 2
    End If

    Sheet3.Range(Sheet3.Cells(currRow, 1), Sheet3.Cells(currRow + rngDB.Rows.Count - 1, rngDB.Columns.Count)).Value = rngDB.Value
    Sheet3.Range(Sheet3.Cells(currRow, rngDB.Columns.Count + 1), Sheet3.Cells(currRow + rngDB.Rows.Count - 1, rngDB.Columns.Count + 1)).Value = rngDB.Cells(-1, 2)

    currRow = currRow + rngDB.Rows.Count

    lastRow = rngSt.Row
    Set rngSt = rngStCol.FindNext(rngSt)
Loop


End Sub

Upvotes: 0

Robert Co
Robert Co

Reputation: 1715

I hate puzzles. At least, that's what I tell people.

Ok. I made the first column as your headers.

A1 =    Account
B1 =    Status
C1 =    Date
D1 =    Invoice
E1 =    Client
F1 =    Index
G1 =    Client Index
H1 =    Client Cnt

Row 2 will be the start of your data.

A2 =    =INDEX(Sheet1!A$1:A$1000,$F2)
B2 =    =INDEX(Sheet1!B$1:B$1000,$F2)
C2 =    =INDEX(Sheet1!C$1:C$1000,$F2)
D2 =    =INDEX(Sheet1!D$1:D$1000,$F2)
E2 =    =INDEX(Sheet1!B$1:B$1000,$G2)
F2 =    =IF(ISNUMBER(F1),IF(INDEX(Sheet1!B$1:B$1000,$F1+1)="",G2+2,F1+1),G2+2)
G2 =    {=IF(H2=H1,G1,SMALL(IF(Sheet1!$B$1:$B$1000="Status",ROW(Sheet1!$B$1:$B$1000),2000),H2)-1)}
H2 =    =IF(ISNUMBER(H1),IF(INDEX(Sheet1!B$1:B$1000,$F1+1)="",H1+1,H1),1)

Paste down until you get #REF errors.

Excel is putting up a stink on the $B:$B syntax. It wants a real range. In addition, you probably want a bigger number than $1000.

After all this, macro is more straight-forward and appropriate for this requirement, but what will be the fun in that.

Enjoy!

Upvotes: 0

nutsch
nutsch

Reputation: 5962

VBA would provide you a much faster answer than a formula, though not dynamic. Run the attached code that filters, offsets and copies the results:

Dim lLastRow As Long, rgResult As Range


lLastRow = Cells(Rows.Count, 2).End(xlUp).Row


With Range("B1:B" & lLastRow)
    .AutoFilter field:=1, Criteria1:="Status"
    Set rgResult = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    .AutoFilter
End With

Sheets.Add

rgResult.Offset(-1).Copy ActiveSheet.Cells(1, 1)

Upvotes: 1

nutsch
nutsch

Reputation: 5962

Try this formula, entered as an array formula, i.e. with Ctrl+Shift+Enter, then copied down.

=INDEX(B:B,IF(ROW()>COUNTIF(B:B,"Status"),"",SMALL(IF($B:$B="Status",ROW($B:$B),2000000000),ROW()))-1)

To run it from another sheet, cut and paste it from your original sheet and it will adjust the reference automatically.

Upvotes: 2

Related Questions