Extracting data from rows

[A17BPG]

[TABLE="width: 30"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Tel.#[/TD]
[TD]PoultryReq[/TD]
[TD]Wght.[/TD]
[TD]PorkReq.[/TD]
[TD]Wght.[/TD]
[TD]BeefReq.[/TD]
[TD]Wght.[/TD]
[TD]Bacon[/TD]
[TD]Wght.[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]123[/TD]
[TD]Turkey[/TD]
[TD]10.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Roasting[/TD]
[TD]3.00[/TD]
[TD]Back[/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]Leg[/TD]
[TD]4.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gammon[/TD]
[TD]4.00[/TD]
[/TR]
[TR]
[TD]Arnold[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Steak[/TD]
[TD]2.00[/TD]
[TD]Streaky[/TD]
[TD]1.00[/TD]
[/TR]
</tbody>[/TABLE]
What I want to do is get data from rows to generate an invoice listing only the data held in rows and ignoring all the empty cells. The results being passed to new sheet called invoice and listed with as follows.[TABLE="width: 20"]
<tbody>[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[TD]Turkey[/TD]
[TD]10.00[/TD]
[TD]@1.20[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Roasting[/TD]
[TD]3.00[/TD]
[TD]@4.50[/TD]
[TD]13.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Back[/TD]
[TD]1.50[/TD]
[TD]@2.50[/TD]
[TD]3.70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]29.20[/TD]
[/TR]
</tbody>[/TABLE]

---

 

[Mike Szczesny]

Hi A17BGG,
Are you asking to produce only the items (turkey,etc.) and weight? I do not see where @1.20, etc. comes into play?

 

[A17BPG]

Hi Mike, the @1.20 etc should be in a separate column "price per kilo" after each "wght" column this allowing for the price to be entered.As showing info was restricted by the number of columns to show I left it out. There are a lot more columns in my sheet 54 in total this is the reason I would like to extract the data from cell used to post to an invoice or even maybe be able to say select all White Turkeys or whatever.

Thanks Brian

 

[Mike Szczesny]

Can you something like this. My formula to present the items is in B6. The formula to present the numbers is in C6. For the formula, you need to use Cntrl+Shift+Enter. The formula is in B6 is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($B$2:$J$4,,SMALL(IF($A$2:$A$4=$A$6,IF(ISTEXT($B$2:$J$4),COLUMN($B$2:$J$4)-COLUMN($B$2)+1)),ROWS($B$6:B6)))," "). The formula in C6 is [TABLE="width: 76"]
<tbody>[TR]
[TD="width: 76"] =OFFSET($B$2,,MATCH($B6,$C$2:$J$2,0)+1,,) Copy your formulas down. Watch your cell references.

[TABLE="width: 656"]
<colgroup><col span="2"><col><col span="5"><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Tel.[/TD]
[TD]PoultryReq[/TD]
[TD]Wght[/TD]
[TD]PorkReq[/TD]
[TD]Wght[/TD]
[TD]BeefReq[/TD]
[TD]Wght[/TD]
[TD]BaconReq[/TD]
[TD]Wght[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD="align: right"]123[/TD]
[TD]Turkey[/TD]
[TD="align: right"]10.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Roasting[/TD]
[TD="align: right"]3.00[/TD]
[TD]Black[/TD]
[TD="align: right"]1.50[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD="align: right"]456[/TD]
[TD][/TD]
[TD][/TD]
[TD]Leg[/TD]
[TD="align: right"]4.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gammon[/TD]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD]Arnold[/TD]
[TD="align: right"]789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Steak[/TD]
[TD="align: right"]2.00[/TD]
[TD]Streaky[/TD]
[TD="align: right"]1.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Turkey[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Roasting[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Black[/TD]
[TD="align: right"]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[A17BPG]

Hi Mike thanks. As on holiday relaxing at moment and in contact via iPad will def try this when get back at weekend. Do you think this could be used from within a Macro ?.

Brian

 

[Mike Szczesny]

Sorry. To be honest, I really am not familiar with macros. I do not use them.

 

[A17BPG]

Hi Mike thanks for your help will try formula you sent.
Brian