making lists????

[PDAUGHE1]

I am trying to take a table and make a few lists. I am not sure how to approach this. I want the results to be within the same excel workbook (not necessarily the same sheet). If it helps, the number of rows will always be the same in the first table (longer than this example). The second (result) table may have different quantities of results in the each column as cells are updated.

Also, note, that there are multiple rows that have similar information in column c.
----- see image upper table

tables.JPG

Would like to have the data above sorted as follows:

---- see image lower table

Please note, the result table would also be referencing the number in the adjacent cell.



Any help would be appreciated!

[daffodil11]

Copy C to another location.

Go to data -> Remove duplicates. Copy, PasteSpecial, Tranpose.

Insert a Column between each.

Assuming this is on Sheet2, you now have the unique headers in A1, C1, E1, etc.
A2:

Please Login or Register  to view this content.

B2:

Please Login or Register  to view this content.

These are both array formulas, confirmed with Ctrl+Shift+Enter.

Once both cells are entered as array formulas, you may copy A2:B2 to C2:D2, and E2:F2 and so on.
Then, drag down as far as needed.

[PDAUGHE1]

Thank you for your response! I am still having trouble with this and am not able to get this to work. I am not fully familiar with these formulas so I have been trying to understand them better but it is not working.

I have updated the files somewhat... attached are images from each sheet. The data is sourced from other pages.

The values listed are importance level... i.e. 10 means high importance and 1 means low importance. A through J are physical site addresses.

If, for simplicity, I need to break this down into tables for parts of the calculations I will.-- if that makes this simpler.
Attachment 387067Attachment 387068

This is how I want it to work: The result on "sheet 2" cell "B3" should be as follows:

------> It should look to column c on sheet 1 and find all rows with "local initiatives"

------> It should then look to column h on sheet 1 to find the highest number (in the same row as local initiatives)

------> It should then state, in cell b3 of "sheet 2" the property type......

------> ------> in this case, it would look and find that 4.93 is the highest value and would state that this refers to "community gardens"

------> It should then, as a result, put the value in cell c3, in this case 4.93

------> If there is nothing designated as a "local initiative" in column c, I would like it to move to column d and then column e

Next, ------> for sheet 2 cell "b4" I would like it to do the same thing but instead find the second highest value in column c (for local initiatives)


Since, at this time, there are only 6 property types, there would only be formulas on sheet 2 for rows 3 though 8

I can repeat the same type of formula in columns D, E, F, G




Can anyone help on this.... Thanks in advance... if you need the file I would be happy to share.

Polly

[PDAUGHE1]

See attached excel sheets:

sheet 1 in my post was "C| Dev 1"
sheet 2 in my post was "C| Dev 2"property use.xlsx

[daffodil11]

Neither of your attachments have been linked to your post correctly.

Go to Reply, Advanced, Manage Attachments, Browse to File, Upload, Hit Okay, then click Post Reply.

[daffodil11]

Can you add a couple of lines to your result, so I can see what is transferring?

[PDAUGHE1]

Were you able to see the excel file I just posted?

[PDAUGHE1]

The "result" sheet- is that what you mean? I haven't even began to develop that page yet.

Every piece of data on "sheet 1" is from other sheets labeled "P| bulletin" and "P|well" etc.

[daffodil11]

Oh, I thought you were directing me there.

So Dev2 then.

B4:

Please Login or Register  to view this content.

C4:

Please Login or Register  to view this content.

Enter both formulas as array formulas, with Ctrl+Shift+Enter.

Then copy B4:C4 and paste into B4:G4.

[daffodil11]

Here's a short run through on that formula if you're so inclined.

[PDAUGHE1]

Wow!

You are a life-saver! I spent hours trying to figure out what you were trying to do earlier- with the formulas.

I will look at this more, but for now, thank you!

Polly

(And.. I will be back I am sure.)

[PDAUGHE1]

sheet new.PNGproperty use 4-2-15.xlsxSee attached files for new questions...