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:

[TABLE="width: 100"]
<tbody>[TR]
[TD][heading 1][/TD]
[/TR]
[TR]
[TD]text 1[/TD]
[/TR]
[TR]
[TD]text 2[/TD]
[/TR]
[TR]
[TD]text 3[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][heading 2][/TD]
[/TR]
[TR]
[TD]text 4[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][heading 3][/TD]
[/TR]
[TR]
[TD]text 5[/TD]
[/TR]
[TR]
[TD]text 6[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 100"]
<tbody>[TR]
[TD][heading 1][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]text 1[/TD]
[TD][heading 1][/TD]
[/TR]
[TR]
[TD]text 2[/TD]
[TD][heading 1][/TD]
[/TR]
[TR]
[TD]text 3[/TD]
[TD][heading 1][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][heading 2][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]text 4[/TD]
[TD][heading 2][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][heading 3][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]text 5[/TD]
[TD][heading 3][/TD]
[/TR]
[TR]
[TD]text 6[/TD]
[TD][heading 3][/TD]
[/TR]
</tbody>[/TABLE]

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!!