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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]100001[/TD]
[TD]2000[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]70001[/TD]
[TD]1000[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]125001[/TD]
[TD]2000[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]

sheet 2:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row Number[/TD]
[TD]Ticket Number[/TD]
[TD]Ticket Number 2[/TD]
[TD]Venue[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100001[/TD]
[TD]1[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]102000[/TD]
[TD]2000[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]2001[/TD]
[TD]70001[/TD]
[TD]1[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]71000[/TD]
[TD]1000[/TD]
[TD]abc
[/TD]
[/TR]
[TR]
[TD]3001[/TD]
[TD]1250001[/TD]
[TD]1[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]1270000[/TD]
[TD]2000[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 990"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]Ticket Type[/TD]
[TD]Starting Ticket Number[/TD]
[TD]Number of Tickets[/TD]
[TD]Ending Ticket Number[/TD]
[TD]Venue[/TD]
[TD]Event[/TD]
[/TR]
[TR]
[TD]Beer Ticket[/TD]
[TD="align: right"]64001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]66000[/TD]
[TD]Main Tent[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Beer Tasting[/TD]
[TD="align: right"]520001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]522000[/TD]
[TD]Beer Tasting[/TD]
[TD]Taste of Tacoma[/TD]
[/TR]
[TR]
[TD]Beer Tasting[/TD]
[TD="align: right"]522001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]524000[/TD]
[TD]Beer Tasting[/TD]
[TD]Taste of Tacoma[/TD]
[/TR]
[TR]
[TD]Beer Tasting[/TD]
[TD="align: right"]524001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]526000[/TD]
[TD]Beer Tasting[/TD]
[TD]Taste of Tacoma[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]488001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]490000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]490001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]492000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]492001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]494000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]494001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]496000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]496001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]498000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]498001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]500001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]502000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]502001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]504000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]504001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]506000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]506001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]508000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]508001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]510000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]510001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]512000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]512001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]514000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]514001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]516000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]516001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]518000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]518001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]520000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]520001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]522000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
[TR]
[TD]Admission[/TD]
[TD="align: right"]522001[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]524000[/TD]
[TD]Blue Gate[/TD]
[TD]Oktoberfest[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.