Lookup in same column

[mgiglio3]

[TABLE="width: 467"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]TENANT & UNIT #[/TD]
[TD]Total SF[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buckhorn Material Handling[/TD]
[TD]21,148[/TD]
[/TR]
[TR]
[TD]Unit: # [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55 W - A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lease Start[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/1986[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lease End[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/31/2019[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hi everyone,

Does anyone know a way to lookup in the same column? I need to return the Name, unit, lease start, and end. to a table that looks like the below. is there a function for this? maybe VBA? I know i can use index and match to return the Square footage.

[TABLE="width: 1096"]
<tbody>[TR]
[TD]Suite[/TD]
[TD]Tenant[/TD]
[TD]Sq. Ft. Leased[/TD]
[TD]Lease Begin[/TD]
[TD]Lease Expiration[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Post some more data.
Your example data doesnt tell us where the next tenant and Unit begins. It could be the next line down, or 2 lines down or 30 lines down.
This looks more like a report, are there any page breaks and headers in amongst the tenant data, we cant tell from what youve posted.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.