Excel - using a range (in one cell) in a formula

[eflan]

Hello,

I have a number of excel spreadsheets related to ticket sales that I'm trying to get organized. In one, we're keeping track of the actual ticket number (the one stamped on the ticket itself). Is there a way to use a range that's in one cell in a formula?

For example, I have number-number (ie 1111-1112) in the cells. Is there a way to extract those numbers in order to get a count? Using extra cells is fine, I would just really like this scripted. Or would I need to manually convert them all into two cells vs just one per ticket type?

I'm taking over some office work for a music festival, and I have a couple hundred incomplete entries that need to get sorted.

Thanks in advance.
Erin

 

[psaus]

If you have a standard 2 values per cell (like your example), you could just count each cell as 2. I have a feeling that it's not consistent (meaning, sometimes you have 1111-1112, and other times you have 1113-1120), otherwise you'd have figured that out on your own.

So, diving deeper...
You could insert a math function between the values. I'm struggling to remember how to get Excel to enter an "=" before the numbers in mass/batch. This would result in a negative number, but you could still add those up at the end, just dropping the negative, and that would be your "count".
The only way I've figured out to do this, that works, is the following:
- Create a .csv file manually. E.g., right click on your desktop or a folder where you want this to exist, select "New/Text Document". Before using this TXT, rename to any name and change the .txt to .csv. If you've done it successfully, it should change from a notepad icon to a funny looking Excel icon.
- Open this new CSV with Notepad NOT Excel.
- Copy your column of number-number and paste it into the CSV file.
- Do a find & replace; In the find field put a single dash/minus '-' Or however the data is separated. In the replace field put a comma plus a single space ', ' then click "replace all"
-Your data should then look like "number, number". If not, modify the above Find&Replace with the right characters to get it that way. If your data is inconsistent, you're going to have a lot of work to make it that way before this will work.
- Once the data looks like number, number save the csv file and close it from Notepad and then open it in Excel. Those two values should now appear in their own cells.
- In cell C1 you can execute your SUM function of cells A1 and B1. Then copy C1 and select cells C2 down to however far you need to go and paste that once.
- If you do the SUM like the following, you will end up with a positive number, so no messing around with cleaning up the data:
=(B1-A1+1)
- Then you can add up all these "counts" in col C and you have your ticket count.

If you wanted to then include this data in your main XLS file, you could just copy and paste columns A:C without messing up the data. Could clean it up in the XLS by hiding the columns that hold the values from A&B from the CSV, showing only the values from C...

I know there are a couple other ways of doing this, but they're escaping me at the moment...
Hope this helps

EDIT: Edited the formula to add a +1, because this is a "counting" exercise, not a "value" exercise.
In the event the person/people who did data entry made the first entry as:1111-1112 and the second 1112-1113 and the third 1114-1115, then there is not going to be an easy way to do this. But as long as nothing overlaps (e.g., 1111-1112, 1113-1115, 1116-1120, 1121-1122) then as stated above will work.

 

[eflan]

Unfortunately, there's going to be overlaps: 3 days of ticket sales with two levels each. Fortunately, each day/level is in its own column... but that means I'll have to do 6 conversions. Yippee.

Thank you a ton for your help - I'll play around with it later on tonight after I'm caught up preparing 3-day passes. I'm sure that this will be an excersise in patience, but it will be easier than trying to go through 150+ entries and doing the math manually.

I REALLY appreciate your help.

 

[psaus]

If you could show some more examples of your data, we might be able to help more. Yes, you gave one example in your OP, but it sounds like there are variations. If you could give those, there might be extra things we can do to help clean up the data...