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]

You don't seem to need sheet 2, a lookup formula can return the ticket numbers from sheet 1 for a given venue.

 

[muhleebbin]

Can you elaborate?

How would i input a number and that it knows that it is part of a series of valid tickets? i'm trying to make this as dummy proof as possible as well. For instance what if someone inputs the number 174056, then it would come back as it wasn't found within the sequence of ticket numbers.

 

[sheetspread]

Something like:

Excel 2010
	A	B	C
2	100001	2000	xyz
3	70001	1000	abc
4	125001	2000	123
5
6
7	174056	#VALUE!
Sheet5

Cell Formulas
Range	Formula
B7	=INDEX($C$2:$C$4,SUMPRODUCT(--($A$2:$A$4<=A7),--($A$2:$A$4+$B$2:$B$4>=A7),ROW($A$2:$A$4)-1))

 

[muhleebbin]

Thank you very much! That was a lot more simpler than my original convoluted thought!

 

[muhleebbin]

Blasted! Ran a test on existing tickets, can you help me solve an issue where one set of tickets overlap another?

For example if row 5 was 126001 1000 xyz

 

[sheetspread]

Are there many such exceptions? If not, you're better off putting it at the end with a new number and a note of the original.

 

[muhleebbin]

We use anywhere from 6 - 10 different types of tickets for events and already ran into an overlap within the first 10 rolls. I’d imagine it’ll happen on occasion, at least enough to ask the question.

 

[sheetspread]

This any better?

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

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

 

[muhleebbin]

This worked, however it can't distinguish on its own whether it was a jkl ticket or 123 ticket. Would adding a fourth column help in this instance?

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]100001[/TD]
[TD]2000[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]70001[/TD]
[TD]1000[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]125001[/TD]
[TD]2000[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]126001[/TD]
[TD]2000[/TD]
[TD]xyz[/TD]
[/TR]
</tbody>[/TABLE]

For the same venue I haven't run into an issue of overlapping ticket numbers as we get rolls usually in sequence. The overlap happens across venues so I added column A for ticket type that I think could help solve this problem?

Can it index first by column A then B?