directional search?

WorkDog

New Member
Joined
Mar 23, 2009
Messages
13
(So this is pretty much the same query as my last, but I've explained it much better this time, I hope - dont worry about the reasons I'm doing this, it might seem a little pointless but I need things formatted in a particular way to import to some software.)


So my spreadsheet looks like this

(everything starts in column B)
---------------------------
. DATA LABEL
<RANDOM info>
. (stuff)
. Time| 0900| ABC
. Time| 0915| DEF
. Time| 0930| GHI

. DATA LABEL
. (stuff)
<RANDOM info>
. Time| 1000| ABC
. Time| 1015| DEF
-----------------------------
the lengths of my tables arent set, could be anything.

Im creating a new worksheet thats gonna look pretty much the same, except I need to add a little redundancy for a piece of software.
------------------------------
. |DATA LABEL1
<RANDOM info>
. (stuff)
DATA LABEL1| Time| 0900| ABC
DATA LABEL1| Time| 0915| DEF
DATA LABEL1| Time| 0930| GHI

. |DATA LABEL2
<RANDOM info>
. (stuff)
DATA LABEL2| Time| 1000| ABC
DATA LABEL2| Time| 1015| DEF
-----------------------------------

Essentially I want my A column formula to look in column C for the word Time. If it finds it, it will look backwards up the B column for the first Data Label entry and use that, if it doesnt, blank cell.

Any Idea how to achieve this?


*edit* where I have a dot, assume an emtpy cell, it keeps removing my deliberate blank spaces.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Try this code:

Code:
Sub a()

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
x = 0
For i = 1 To lastrow

    If Cells(i, 2).Value = "DATA LABEL" Then
        On Error Resume Next
        x = x + 1
        Cells(i, 2).Value = "DATA LABEL " & x
        Set c = Range("B" & i + 1 & ":A" & lastrow).Find("DATA LABEL", LookIn:=xlValues, Lookat:=xlWhole)
             If Not c Is Nothing Then endrow = c.Row
        On Error GoTo 0
    Else
    If Cells(i, 2).Value = "Time" Then
    Cells(i, 1).Value = "DATA LABEL " & x
    End If
    End If

Next i


End Sub
 
Upvote 0

Forum statistics

Threads
1,203,081
Messages
6,053,416
Members
444,662
Latest member
AaronPMH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top