Allow data to be easily filtered from multiple headings in a single column

[cgillies]

Hi all,

I am open to any suggestions here.. I have an lot of data that is from a config file that I paste into excel. The data looks like this:

[heading 1]

text 1

text 2

text 3

[heading 2]

text 4

[heading 3]

text 5

text 6

<tbody>
</tbody>

I need to be able to filter the date by heading to show all the entries under [heading 1] etc. I have been thinking of trying to add a column next to the data and copying the heading next to the appropriate text, so it would look like this:

[heading 1]
text 1	[heading 1]
text 2	[heading 1]
text 3	[heading 1]
[heading 2]
text 4	[heading 2]
[heading 3]
text 5	[heading 3]
text 6	[heading 3]

<tbody>
</tbody>

I have blank rows, which I can remove if needed, else the formula should skip these. I have been experimenting with formulas that look for the text starting with "[", copy the cell value to the new column, if it does not match then search in the rows above to return the value of the cell containing [.

The first part of the formula is easy enough:

=IF(ISNUMBER(SEARCH("[",A1,1)),A1,"")

But I am having issues with the 2nd part of the logic. Any help would be greatly appreciated - or f anyone has another suggestion on a better way to get my filtering result I am open.

Thanks in advance,

Cameron

 

[Johnny C]

You need a running version, you're nearly there
=IF(A1="","",IF(ISNUMBER(SEARCH("[",A1,1)),A1,""))
in col B use the formula (from row 2 down)
=IF(A2="","",IF(ISNUMBER(SEARCH("[",A2,1)),A2,B1))
You could make it one formula using ROW() and OFFSET but that's overkill unless it's going to be used elsewhere

 

[cgillies]

Hot **** !! Thanks so much for a quick and simple response. My faith in humanity is restored. Really really appreciate it!!