Fill values based on a starting value in one cell with the number in another cell (macro?)

[muhleebbin]

Hey all,

I deal with a lot of ticket rolls in my industry and was wondering if there's a way to simplify counting the number of tickets used for a venue.

On sheet 1 i'd like to have column a be a starting value of ticket #, column b the number of tickets in the roll (i.e. 1000 or 20000) and column c be the venue. On sheet 2, i'd like column a to be filled with row 1 the starting value and all rows filled with the series of numbers up until the value of column b in sheet 1. Column b (in sheet 2) to be filled with 1 - (value of column b sheet 1), and column c filled with the venue.

Sheet 3 eventually is my lookup tool for starting number vs ending number using vlookups to find the number of tickets used for a particular venue on a particular day.

example below

sheet 1:

100001	2000	xyz
70001	1000	abc
125001	2000	123

<tbody>
</tbody>

sheet 2:

Row Number	Ticket Number	Ticket Number 2	Venue
1	100001	1	xyz
2000	102000	2000	xyz
2001	70001	1	abc
3000	71000	1000	abc
3001	1250001	1	123
5000	1270000	2000	123

<tbody>
</tbody>

Any other suggestions on how to tackle this would be much appreciated as well. Thanks in advance

 

[sheetspread]

Yes:

Excel 2010
	A	B	C	D
1
2	A	100001	2000	xyz
3	B	70001	1000	abc
4	C	125001	2000	123
5	A	126001	1000	jkl
6
7	A	126002	jkl
Sheet5

Cell Formulas
Range	Formula
C7	{=INDEX($D$1:$D$5,SUM(IF($B$2:$B$5<=$B7,IF(($B$2:$B$5+$C$2:$C$5)>=$B7,IF($A$2:$A$5=A7,ROW($B$2:$B$5))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[muhleebbin]

This works perfectly! Thank you so much for your help on this

 

[muhleebbin]

Spoke too soon

This array formula, for some reason, hates the first number in a roll. If line 4 column a is changed to "A", inputting 126001 in b7 will result in a #REF ! error. It looks like the sum portion of the formula is finding both rows 4 & 5 because they're both true in this instance.

 

[sheetspread]

You mean this?

Excel 2010
	A	B	C	D
1
2	A	100001	2000	xyz
3	B	70001	1000	abc
4	A	125001	2000	123
5	A	126001	1000	jkl
6
7	A	126002	#REF!
Sheet12

Cell Formulas
Range	Formula
C7	{=INDEX($D$1:$D$5,SUM(IF($B$2:$B$5<=$B7,IF(($B$2:$B$5+$C$2:$C$5)>=$B7,IF($A$2:$A$5=A7,ROW($B$2:$B$5))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Of course, that was the reason for having two different letters. Otherwise how can you distinguish the two?

 

[muhleebbin]

In some instances we'll get 30,000 tickets where they're sequential. 125001 - 165000 so we'll have rolls of 2000 with 125001, 127001, 129001 and so on. They're all the same ticket type but 127001, 129001 etc will all have issues within this formula. Even if you change row 4 to A, 125001, 1000 jkl it'll still bring up a #REF ! error. 126002 will now work but 126001 will bring up a #REF ! error

Sorry for the inconvenience and as always grateful for your help.

 

[sheetspread]

Because there are duplicate (non-unique) rows. The formula in post #9 returns a list of venues, are you sure that doesn't work for you?

 

[muhleebbin]

Post 9's formula worked, however we do have overlapping numbers of ticket types which is why I added column A in post 10. Below is actual data i'm working off of. This is just a sample size but as you can see the Beer Tasting tickets started running into the Admission tickets. This is where I ran into issues and it's only the starting numbers as that brings in values in the first two 'if' formulas

Ticket Type	Starting Ticket Number	Number of Tickets	Ending Ticket Number	Venue	Event
Beer Ticket	64001	2000	66000	Main Tent	Oktoberfest
Beer Tasting	520001	2000	522000	Beer Tasting	Taste of Tacoma
Beer Tasting	522001	2000	524000	Beer Tasting	Taste of Tacoma
Beer Tasting	524001	2000	526000	Beer Tasting	Taste of Tacoma
Admission	488001	2000	490000	Blue Gate	Oktoberfest
Admission	490001	2000	492000	Blue Gate	Oktoberfest
Admission	492001	2000	494000	Blue Gate	Oktoberfest
Admission	494001	2000	496000	Blue Gate	Oktoberfest
Admission	496001	2000	498000	Blue Gate	Oktoberfest
Admission	498001	2000	500000	Blue Gate	Oktoberfest
Admission	500001	2000	502000	Blue Gate	Oktoberfest
Admission	502001	2000	504000	Blue Gate	Oktoberfest
Admission	504001	2000	506000	Blue Gate	Oktoberfest
Admission	506001	2000	508000	Blue Gate	Oktoberfest
Admission	508001	2000	510000	Blue Gate	Oktoberfest
Admission	510001	2000	512000	Blue Gate	Oktoberfest
Admission	512001	2000	514000	Blue Gate	Oktoberfest
Admission	514001	2000	516000	Blue Gate	Oktoberfest
Admission	516001	2000	518000	Blue Gate	Oktoberfest
Admission	518001	2000	520000	Blue Gate	Oktoberfest
Admission	520001	2000	522000	Blue Gate	Oktoberfest
Admission	522001	2000	524000	Blue Gate	Oktoberfest

<colgroup><col span="6"></colgroup><tbody>
</tbody>

 

[muhleebbin]

Actually I kind of solved the problem, just needed to change the number of tickets to 1999 and it solves the overlap in the starting number

 

[sheetspread]

Ok good, post back if it need adjusting.