Transpose to columns, change format of a table

[wilsong100]

I have a table that I extracted from ArcGIS (see an extract of the table below). It features 12 designated sites and within each of the sites there are occurences of up to 63 different feature types.

NAME	FEATURE	FREQUENCY
Antrim Hills	Heather	15
Antrim Hills	Scrub	38
Larne	Beaches	3
Larne	Rock	17
.....and so on

<tbody>
</tbody>

What I want to do is list the feature types as columns and the designated sites as rows. The values will contain the occurence of a feature within a designated site (see table below).

Table I would like to achieve:

	Beaches	Bracken	Heather	....and so on
Antrim Hills	0	0	15
Larne	3	0	0
Lough Foyle	15	1	2
....and so on

<tbody>
</tbody>

Stumbling blocks are that there are 63 features I would like columns for and some designated sites do not have an occurence of all 63 features and unfortunately zero values were not recorded. So the data for designated sites only include data for the features that occur within them.

Much appreciated for any help on this.
Cheers

 

[hiker95]

wilsong100,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Excel 2007
	A	B	C	D
1	NAME	FEATURE	FREQUENCY
2	Antrim Hills	Heather	15
3	Antrim Hills	Scrub	38
4	Larne	Beaches	3
5	Larne	Rock	17
6
Sheet1

Questions in reference to the above screenshot:

1. What is the real worksheet name?

2. What cell contains NAME?

3. Where do you want the results to be written to?

3a. In the same worksheet, off to the right?

3b. If so, what cell contains Beaches?

4. Can we have a complete list of the unique NAME's?

5. Can we have a complete list of the FEATURE's?


To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[azumi]

Pls find this link, im make the formula based on your table, just adjust for your actual workbook:

https://www.dropbox.com/s/qcjyiqjspxv74j7/Book1-Az.xlsx?dl=0

hope this helps

 

[wilsong100]

Hi Azumi,
Your solution looks good. I will try it in work on Monday and let you know how it went.
Cheers for your help.

 

[hiker95]

azumi,

I usually try to solve requests with macros.

I have not used array formulae like you have on this thread in a very, very, long time.

Your solution is now in my archives for future use.

Thank you very much.

 

[hiker95]

wilsong100,

Here is a macro solution for you to consider.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	FEATURE	FREQUENCY
2	Antrim Hills	Heather	15
3	Antrim Hills	Rock	11
4	Antrim Hills	Scrub	38
5	Larne	Rock	17
6	Larne	Beaches	3
7	Larne	Bracken	22
8	Lough Foyle	Beaches	15
9	Lough Foyle	Bracken	1
10	Lough Foyle	Heather	2
11
12
13
14
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	FEATURE	FREQUENCY				Beaches	Bracken	Heather	Rock	Scrub
2	Antrim Hills	Heather	15			Antrim Hills	0	0	15	11	38
3	Antrim Hills	Rock	11			Larne	3	22	0	17	0
4	Antrim Hills	Scrub	38			Lough Foyle	15	1	2	0	0
5	Larne	Rock	17
6	Larne	Beaches	3
7	Larne	Bracken	22
8	Lough Foyle	Beaches	15
9	Lough Foyle	Bracken	1
10	Lough Foyle	Heather	2
11
12
13
14
Sheet1

Then you add three more rows of raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	FEATURE	FREQUENCY				Beaches	Bracken	Heather	Rock	Scrub
2	Antrim Hills	Heather	15			Antrim Hills	0	0	15	11	38
3	Antrim Hills	Rock	11			Larne	3	22	0	17	0
4	Antrim Hills	Scrub	38			Lough Foyle	15	1	2	0	0
5	Larne	Rock	17
6	Larne	Beaches	3
7	Larne	Bracken	22
8	Lough Foyle	Beaches	15
9	Lough Foyle	Bracken	1
10	Lough Foyle	Heather	2
11	Red Mtn	Bracken	45
12	Red Mtn	Heather	23
13	Red Mtn	Red Rock	33
14
Sheet1

And, run he macro again:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	FEATURE	FREQUENCY				Beaches	Bracken	Heather	Red Rock	Rock	Scrub
2	Antrim Hills	Heather	15			Antrim Hills	0	0	15	0	11	38
3	Antrim Hills	Rock	11			Larne	3	22	0	0	17	0
4	Antrim Hills	Scrub	38			Lough Foyle	15	1	2	0	0	0
5	Larne	Rock	17			Red Mtn	0	45	23	33	0	0
6	Larne	Beaches	3
7	Larne	Bracken	22
8	Lough Foyle	Beaches	15
9	Lough Foyle	Bracken	1
10	Lough Foyle	Heather	2
11	Red Mtn	Bracken	45
12	Red Mtn	Heather	23
13	Red Mtn	Red Rock	33
14
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub CreateUpdateTable()
' hiker95, 02/07/2015, ME834139
Dim lra As Long, lrf As Long, lrg As Long, luc As Long
Dim c As Range, n As Range, f As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
  luc = .Cells(1, Columns.Count).End(xlToLeft).Column
  If luc > 3 Then .Columns(6).Resize(, luc - 5).ClearContents
  lra = .Cells(Rows.Count, 1).End(xlUp).Row
  .Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns(6), Unique:=True
  .Cells(1, 6).ClearContents
  lrf = .Cells(Rows.Count, 6).End(xlUp).Row
  .Range("F2:F" & lrf).Sort key1:=.Range("F2"), order1:=1
  .Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns(7), Unique:=True
  .Cells(1, 7).Clear
  lrg = .Cells(Rows.Count, 7).End(xlUp).Row
  .Range("G2:G" & lrg).Sort key1:=.Range("G2"), order1:=1
  .Range("G1").Resize(, lrg - 1).Value = Application.Transpose(.Range("G2:G" & lrg))
  .Range("G2:G" & lrg).ClearContents
  .Range(.Cells(2, 7), .Cells(lrf, 7 + lrg - 2)) = 0
  For Each c In .Range("A2:A" & lra)
    Set n = .Columns(6).Find(c.Value, LookAt:=xlWhole)
    Set f = .Rows(1).Find(c.Offset(, 1).Value, LookAt:=xlWhole)
    If (Not n Is Nothing) * (Not f Is Nothing) Then
      .Cells(n.Row, f.Column) = c.Offset(, 2).Value
    End If
  Next c
  .Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CreateUpdateTable macro.

 

[azumi]

azumi,

I usually try to solve requests with macros.

I have not used array formulae like you have on this thread in a very, very, long time.

Your solution is now in my archives for future use.

Thank you very much.

Cheers hiker95