Sorting column so values move next to like values in 2nd col

duglazz

New Member
Joined
Feb 10, 2003
Messages
10
There are two sample spreadsheets below. The "current" and the "desired"

My real spreadsheet will have 5000 rows.

I work for a baseball team's ticketing office, and i need to scan the barcodes from the tickets of full season ticket holders into this spreadsheet after every game. Each column will be used for 1 game, and contain all the scanned barcodes (a barcode will consist of the section/row/seat)

After scanning the tickets after the game, my spreadsheet looks like the "current" example below, but i want it to look like the desired. The game1 and game2 columns contain all the barcodes that tell me who showed up to that game.

I want to sort the game1 and game2 columns in the "current" example so that the seatID will appear next to the person's name in the row with the same seat ID, as shown in the "Desired" spreadsheet.

In the spreadsheet, Todd is the only person to show up for both games, and i can see that easily because the 2 "9a"'s are next to the his name and his seat for the season (9a) in the "desired" spreadsheet.

I don't know how to sort the game1 and game2 columns so that they can match (and be on the same row as) the value in the second column. I hope someone can follow and help! Thanks
attendance spreadsheet.xls
ABCDEFGHI
1CurrentDesired
2NameSeatGame1Game2NameSeatGame1Game2
3Doug1a9a6aDoug1a1a
4Jane2a12a3aJane2a2a
5Bob3a1a9aBob3a3a
6Rick4a7aRick4a
7Tom5a2aTom5a
8Bill6aBill6a6a
9John7aJohn7a7a
10Josh8aJosh8a
11Todd9aTodd9a9a9a
12Matt10aMatt10a
13Cate11aCate11a11a
14Tony12aTony12a12a
15Dave13aDave13a
Sheet3
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

FryerTuck

New Member
Joined
Jan 23, 2004
Messages
37
A simple solution:
Scan the barcodes into another (unused) column (in my sample, I used column "E"

In the game column, I copied the following formula into all cells:


=IF(ISERROR(MATCH(B2,$E$2:$E$15,0)),"",B2)

If there is a perfect match between the seat number and any of the barcode date, the seat number will show, otherwise you will get a blank. Just make sure you copy the info as a value rather than a formula before you put the next set of barcodes into column E.

Home Run?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,058
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top