PDA

View Full Version : Special Sort needed - Sorting labels contained in one column



blumfeld0
02-26-2013, 05:55 PM
Hi!

I am doing a project at work and because the data is proprietary I can't post the actual. Here is the issue:

I have one column and thousands of rows (with values between 0 and 100) and another column with the same amount of rows with labels. Please see the sample attached spreadsheet. The problem is that the second column has hundreds of labels with each numerical value. I want to do a few calculations but first I want the data presented in a way such that each numerical value matches the corresponding label.

I think the attached spreadsheet will explain everything. Please let me know if you can help. Please keep me in mind that I need to do for data which has 2 columns and thousands of rows (and hundreds of different labels).

I would greatly appreciate any assistance. Thank you!

werafa
02-27-2013, 02:52 AM
Use a pivot table.

this can be constructed to put your labels as row headers, rather than beside your data - should be the same end result though

blumfeld0
02-27-2013, 03:54 AM
Hi - Yes Thank you for the response I realized that after I posted the message too. But I am not very familiar with pivot tables. I tried playing with it last night and could not get it to work?

Would someone mind taking the attached spreadsheet and creating a pivot table with the sample raw data? That way I will know how to reproduce it.

The other problem with pivot tables is that calculations I need to perform may not be so easy. For example I need to calculate the 10% percentile for each label (and like I said I have hundreds of labels) and each label has a random number entries (rows). That is why I prefer not to use pivot tables but who knows there may be a way to use pivot tables to do this as well....

Thank you in advance for all your help.

werafa
03-03-2013, 10:52 PM
For a start, play with dropping different fields into your row and column areas. the pivot table interface is very different, depending on what version of office you have - so look on the internet for a pivot table tutorial that suits you. You can also add 'calculated fields' (at least in later versions of office). This is also a little tricky, and is somewhat hidden in office 2007-2010, and is also well worth finding a good tutorial and working though the examples. I strongly suspect that any valid (normal) excel function would work here - but have yet to fully explore this.

Tim

SamT
03-04-2013, 09:13 PM
Is speed a consideration? IOW, will you need to do this very often?

One way to get a unique list of labels is to go down the column and add a dummy Item to a dictionary with the label as the Key. Put an "On Error Resume Next" before the Add method. then set an array = Dictionary.Keys.

I don't see any good way to get your results, other than to iterate thru the columns for each key in the array. Well, I do, but it would require a class module.

I suppose you could use Forms.Listboxes to hold the values until you're ready to paste them into the worksheet.