Help needed consolidating lines

[plusoneservices]

Hi all. I have an issue where i have several lines on a spreadsheet
where i want to consolidate it onto one line.

Sounds simple i know, please see the attached file as it explains my
situation far more clearly. (example.jpg).

Many thanks
Darren


+-------------------------------------------------------------------+
|Filename: example.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4199 |
+-------------------------------------------------------------------+

 

[Max]

Here's one approach to play with ..

We'll create a pivot table (very quickly*), then just apply
some simple formulas to pull out the desired table
*Requires only a few clicks and drags & drops <g>

Sample construct available at:
http://www.savefile.com/files/5682246
Consolidating_Lines_plusoneservices_gen.xls

Create Pivot Table (PT):
Select any cell within the source table in A1:B13
Click Data > PivotTable Report
Click Next > Next

In step 3 of the wizard,
Drag & drop PLONO within the ROW area
Double-click on PLONO > Set Subtotals to "None" > OK

Drag & drop SECTION within the COLUMN area
Double-click on SECTION > Advanced
Check "Descending" under AutoSort options > OK

Drag & drop SECTION within the DATA area
(it'll appear as Count of SECTION)
Click Next

In step 4,
Check "Existing worksheet", enter: =$D$1
Click Finish

(The PT will be created in D1:H8)

Then, to pull out the desired results:
Put in J2: =D2, copy J2 down to J7
Put in K3: =IF(E3=1,E$2,"")
Copy K3 across to M3, fill down to M7 to populate

The above yields in J2:M7 :

PLONO
10697 LEGL CONT
18520 LEGL CONT
21424 LEGL CONT
21495 LEGL CONT BLDG
23275 LEGL CONT BLDG

 

[plusoneservices]

Thank you, this is fantastic. Your help is much appreciated!!

 

[plusoneservices]

Thank you, this is fantastic. Your help is much appreciated!

 

[Max]

You're welcome !