Hi all,

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

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
recreation).

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

Alex

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:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...les/xlconPT101.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"australand" wrote in message
...
Hi all,

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

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
recreation).

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

Alex

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
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"australand" wrote:
This has probably been answered before in one way or another, but I'll ask
anyway..

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
recreation).

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

Alex

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 :)
--
Regards,

Alex


"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"australand" wrote:
This has probably been answered before in one way or another, but I'll ask
anyway..

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
recreation).

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

Alex

Max,

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

Alex


"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"australand" wrote:
This has probably been answered before in one way or another, but I'll ask
anyway..

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
recreation).

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

Alex

Welcome, glad it worked out great for you.
Thanks for feeding back
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"australand" wrote in message
...
Max,

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

Alex