PDA

View Full Version : Solved: Copying unsorted data to a sorted order in a new sheet



mike31z
04-30-2007, 09:12 AM
5605 This is a copy of my spreadsheet.


Problem: To copy data from one work sheet that is not in alpha/numeric order to another work sheet that must be in numeric order. My excel file has 5 individual worksheets


Registration (This sheet is were the registration for all individuals that are participating)
Singles (This sheet is used to record the individual scores in squad order)
Doubles (This sheet is used to record the individual scores in squad order)
Handicap (This sheet is used to record the individual scores in squad order)
Names (This sheet is used to record the list needed to aid in sorting after the event and during registeration)

On the Reg worksheet I color the row header into three groups to show which cell data goes into which named worksheet.


I tried to use Vlookup but the source data must be in numerical sequence. What I don?t want to do is have to resort data when moving between different work sheets.

I have fill in some data fields for about 20 people across all events. I left the ata Number column blank may be used in ongoing developments.

Once I get the names into the correct worksheet I can create a number of macro to re organize the data in to the sequence needed to that work sheet.

I have some simple math formulas that are not locked.

This is for our local sporting club.

mike31z
04-30-2007, 11:46 AM
What I need is the ability to do a HLookup were the left most column is not in numerical.

The "Reg" work sheet has all the data that needs to be looked up and posted to the other three sheets. Since I posted the message above I put a new colums in after the Squad column on the Reg sheet that is linked to the name column. This may or may not help.

The squad column on the other work sheets is the olny constant.

mdmackillop
04-30-2007, 11:58 AM
Hi Mike
FYI VLookUp can deal with unsorted data. Add the False paramater as in
=VLookup(A1,B1:C100,2,False)

mdmackillop
04-30-2007, 12:22 PM
Here's an approach using a "Helper" column O to store a copy of the names. This lets you use VLookup based on the squad numbers on the Reg sheet to return relevant information. Let us know how you want to proceed.

mike31z
04-30-2007, 02:04 PM
That all I needed Maybe I just did get it correct the first time but that is what I needed.

Thank You much

mike in wisconsin