Filtering unfiltered data into array

[riwiseuse]

I tried this once before for another project and never really got a solution, because I made the question one of changing rows to columns which is not precisely what I'm trying to do.

I have raw data for a number of precincts voting results.

Unfortunately, it is just one long set of records with

Precinct Number, Number of Votes, Candidate Name

so sample data looks like this

725,874,Candidate Elephant Mayor
725,1025,Candidate Donkey Mayor
725,650,Candidate Moose Mayor (I'm from RI where we have the Cool Moose Party)
726,1065,Candidate Elephant Mayor
726,780,Candidate Donkey Mayor
726,825,Candidate Moose Mayor
725,1015,Candidate Elephant City Council
725,937,Candidate Donkey City Council
725,705,Candidate Moose City Council
726,957,Candidate Elephant City Council
726,831,Candidate Donkey City Council
726,1054,Candidate Moose City Council

So what I'm trying to accomplish is to query the first column for unique precinct numbers and place those as unique entries as Row labels in column A on sheet 2 which currently has not data in it.

then I want to query the third column for unique entries and make those into column labels in Row 1 on sheet 2.

Then I need to have a macro run through the data and match Row and Column headings for each record in the original data in order to know where to place the actual data, i.e. the number of votes from column 2 in the original data, in the array I've created on Sheet 2.

This is the second project I've run into this where data is delivered as a runon rather than already separated into fields across the range of data labels.

Hope I have helpfully titled this request and I always appreciate the thoughtful attention that members of this forum and the moderaters devote to such questions.

Brian

[StephenR]

I don't think you need macros. You could split the data into separate columns using Text to Columns (comma delimited) and then use a pivot table (add a header row first).

[riwiseuse]

I don't think you need macros. You could split the data into separate columns using Text to Columns (comma delimited) and then use a pivot table (add a header row first).

Stephen,

I think I tried this the last time and never got anywhere. I don't understand how to make a pivot table. The "Text to Column "wizard on its own seems useless. If I'm importing text from a single column why do I need delimiters at all? None of the advanced functions or other wizard screens seem to offer to organized the date by filtering for values in other columns in the row and if you select more than one column you get a dialogue box that says "text to column" only works on a single column.

I need to create unique column and row labels and put the date at the correct interesection in this array. It seems that you may be considering what I'm trying to do but I just don't understand what a pivot table is or how to incorporate it in my work with the "text to column" command.

e.g. in the example I posted I would want Sheet 2 data to come out like this

ARRAY E. Mayor D. Mayor M. Mayor E. Council D. Council M. Council

725 874 1025 650 1015 937 705
726 1065 780 825 957 831 1054

while I can imagine a smart filter text to column function doing that, I sure can't figure out how to make it happen using the "text to column" command.

Maybe it has better filter function and I'm just not aware of it.

Thanks,

Brian

[StephenR]

Brian - please attach a workbook with a decent sample of data and illustration of your desired results. We can take it from there.

[riwiseuse]

Brian - please attach a workbook with a decent sample of data and illustration of your desired results. We can take it from there.

If all goes well, there are two files attached here.

One is how the data comes, in a stream.

The other is the kind of organization I'm trying to create from that.

There are actually more precincts and more races invovled, but I put up two precincts and two races to illustrate.

I understand this approach will create a fair number of columns because there are a fair number of races. I could eventually see using 3D sheets to make a result array by party with columns for each office, although now I have a single array with a column for each candidate for each office.

If I can get as far as automating the process to accomplish what I did by cutting and pasting in the files attached, I'd be a long way towards my goal.

Thanks,

Brian

[StephenR]

As far as I can see you can do that with pivot tables. Your initial sheet was already in separate columns so didn't need delimiting. If you add a header row (anything will do) and then do a pivot table with column A as the row field, col C as the column field and col B as the data field. There are, however, other approaches.

[riwiseuse]

As far as I can see you can do that with pivot tables. Your initial sheet was already in separate columns so didn't need delimiting. If you add a header row (anything will do) and then do a pivot table with column A as the row field, col C as the column field and col B as the data field. There are, however, other approaches.

I am unfamiliar with what a pivot table is or how to effect one. I might guess form the name that it helps define the spread of data as you swing it from one long column across several.

Is there a command or is it part of the text to column wizard I have missed, or ???

thanks,

brian

[StephenR]

To save reinventing the wheel, here is a tutorial on pivot tables for xl 2007:

http://www.contextures.com/CreatePivotTable.html

Here's one for xl 2003:

http://www.edferrero.com/ExcelTutori...9/Default.aspx

Come back if none of it makes sense.

[riwiseuse]

To save reinventing the wheel, here is a tutorial on pivot tables for xl 2007:

http://www.contextures.com/CreatePivotTable.html

Here's one for xl 2003:

http://www.edferrero.com/ExcelTutori...9/Default.aspx

Come back if none of it makes sense.

Stephen

thanks, I found the command by dead reckoning. I'll go to these tutorials to burnish my rough pivot table credentials. When I first tried it only used one of the column labels from the column I had dragged into the graphical column area for allocation. Although there were distinct groupings that should have made column headings, they came over as immensely long strings with spaces and odd nonsense from the data. When I clean those up and made a header row with a single title and redid the pivot table, it worked perfectly. Not only that, It was able to create subcolumns, so when i reformatted the labels I made two columns, one for the race, e.g. Mayor, and one for the name of the candidate.

In the wizard I dragged these first one and then the other into the 'Column Area' at the top of the graphical interface and it was seamless.

Thanks,

Brian