How to extract data from one column into two separate columns



Hi all,

This has probably been answered before in one way or another, but I'll ask

I work for a Land Developer and often need to extract data from a CAD
package into an excel table; this information relates to different areas
within each land subdivision.

The two main pieces of data extracted are as follows:
1) Overall Area of Subdivided Lot
2) Area within Subdivided Lot used for recreational purposes

I have set up the CAD software to extract the above information in the
following form (example shown below), which I can then save out as a text
file to import into Excel (into a sheet called "Site-Analysis"):

No 001 133.22 SIPO
No 001 355.292 SILO
No 002 53.222 SILO
No 003 35.025 SIPO
No 003 108.311 SILO
No 004 73.962 SIPO
No 004 212.112 SILO
No 005 64.357 SIPO
No 005 234.047 SILO

The first column is the Subdivided Lot number, the second column are the
areas and the third column identifies what each area is (there are only two
as noted in the beginning - SILO: Overall area, SIPO: Area used for

What I need to do is to arrange the areas shown above into two separate
columns, one showing each lot's overall area and the other showing just each
lot's recreational area. These columns would be located in a sheet called
"House-Data". This information would be shown as indicated below:

Lot Site Area Recr. Area
1 355.292 133.22
2 53.222
3 108.311 35.025
4 212.112 73.962
5 234.047 64.357

Using the INDEX function I have been able to separate these two areas.
However I cannot work out how to arrange each area to their corresponding
"Lot". At the moment it will not add that empty cell to "Recr. Area" (as
there is no recreational area to "Lot" 2) therefore the areas end up not
corresponding to the right "Lot".

Help with this would be so greatly appreciated!



Bernard Liengme

A great place to learn about pivot tables; have at look at these sites then
come back it you need more info

Debra Dalgleish's pictures at Jon Peltier's site:
And Debra's own site:

John Walkenbach also has some at:
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:

MS has some at (xl2000 and xl2002):

best wishes


Try the power and ease of a pivot table ..
Assume your data as posted has col headers: Lot, Area, Type
Create a pivot, in Layout, place "Lot" and "Type" in ROW, Area in DATA
(it'll show as Sum of Area). Click to finish. In the pivot sheet, doubleclick
on "Lot", set Subtotals to none. Then drag "Type" and drop over "Total" to
re-arrange it into columns. That's it. The results will be what you seek.
Any good? hit the YES below
Downloads:27,000 Files:200 Subscribers:70


Bernard and Max,

Thanks for your suggestion guys, I will give Pivot tables a go and based on
the results i will provide you with feedback Max :)



Just one word for you... LEGEND!!
It worked a treat, did exactly what I needed it to do.




Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question