Search : in
By :

Excel: matching and sorting

Last answer on Jul 27, 2009 4:08:36 pm BST joecrosstime, on Sep 4, 2008 5:25:37 pm BST 
 Report this message to moderators

Hello,
Ok, how sould a computer dummy to this (the dummy is me):
I have a list of 300 bike racers. In the columns next to the racer is items such as bib numbers, team, age, etc.
For example:
John Smith / #52 / Eagles Racing Team / 42 / San Francisco
When the race is over, the officials will give me a list of results in order by bib number, like this:
#47
#66
#67
What I'd like to do is enter those numbers in a colunm on the far left, next to a column which I've alread set up so that it will look like this:
1 / #47
2 / #66
3 / #67
Then I'd like the computer to match the information for #47 with the rest of the information about #47 and down the list.
Do you know how I can do this?

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « Excel: matching and sorting » in :
Basic Excel Formulas 2 ShowBasic Excel Formulas 2 We saw the SI function Function and is also interesting. Combined with SI, this gives: = IF (AND (condition1; condition2 ;....... conditionZ) true false) displays on the various conditions to be verified (eg...
[Excel] Sorting rows only Show[Excel] Sorting rows only Microsoft Excel by default perform a sort on the columns (Data/Sort). To sort the data horizontally, just select the data to be sorted, then Data tab /Sort/ Options/ check the Sort Left to right box in the...
[Pascal language] Recursion within a Bubble Sort Show[Pascal language] Recursion within a Bubble Sort Pascal is a stable, efficient and block-structured programming language. The "type" of variables used in Pascal language is made up of its semantic nature and its range of values, and can...
Spreadsheets - Data Entry ShowCell Content A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics: a type, which means the intrinsic type of the data. There are generally three types of values: numeric values, for example...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Ask your question Reply

1

Jimmy, on Sep 6, 2008 2:55:29 pm BST
  • +3

Okay,

To solve this you need to use a lookup table for example:

Say you have headings for the podium finishers in B1 Bib #, C1 Name, D1 Team, E1 Age, F1 Home Base
then you have A2, A3, and A4 showing 1st, 2nd, and 3rd and the podium finsishers listed in B2, B3, and B4 respectively.

Then your table of 300 bike racers data is in columns J2 through N302 in the same order as the podium finishes above. It is important to have the bib numbers first as this is what you will be using to search the data on.

The formula you will be using in cells C2 thru F4 for the podium finishers details will be:
=vlookup(lookup value, data table arrary, column index number,)

where the lookup value will be the bib number in the above case b2, b3 and b4. The data table array will be J2:N302 and the column index number will be 2 for name, 3 for team, 4 for age and 5 for home base (keep in mind 1 is for the bib number you already have) then ensure you have a "," (comma) at the end before the close brackets.

For more information look up the help file for VLOOKUP tables.

Also just a thought, for better data accuracy instead of storing the age you could store their DOB then you wouldn't have to update the sheet for all riders each year.

   
Reply to Jimmy

2

Syd, on Dec 19, 2008 1:09:37 am GMT
  • +4

Hi Jimmy
I am looking to match information in one column with another and came accross your advice to joecrosstime.

I sent out faxes to over 800 different numbers and have them listed in an excell spreadsheet with names in A and fax numbers in B.

My fax service provider sent my message to each but 150 failed. I have a list of the failed numbers in C and need to match them to the cells in A and B so that i can follow on the faxes that failed.

How do i get the numbers in C to to match the numbers in B?

Thanks

syd

   
Reply to Syd

4

Matt_NZ, on Feb 4, 2009 9:30:22 pm GMT
  • +2

If all you need is to match up the names from column A to the numbers in Column C (via numbers in Column B) then this should do the trick...

In Column D enter the function =INDEX($A$1:$A$800,MATCH($C1,$B$1:$B$800,0))... then just drag this down the cells to auto fill for the rest of the 150 rows.

What it is doing is Matching the value in C1 (bad number), with the values between A1 to A800 (original list of sent numbers). Once the MATCH function gets a match, it returns a row reference, which the INDEX function uses to give you the value in Column A (the name).

Hope this is what you were after?

   
Reply to Matt_NZ

6

 JotaPe, on Jul 27, 2009 4:08:36 pm BST
  • +1

It worked for me! Thank you very much!

   
Reply to JotaPe

3

karthik, on Jan 22, 2009 3:35:42 pm GMT
  • +2

Ya
i am also searching for a solution for this matching problem . can anyone fpost a solution please?

   
Reply to karthik

5

edgar, on May 4, 2009 12:54:35 am BST
  • +7

Jimmy's answer is unclear and very confused. You should try to download a special software to match the cells you want such as Excel Joint Merge Two Tables, etc

   
Reply to edgar
Ask your question Reply
They need your help