Formula Help Needed

[excel_training_wheels]

Hi--

I have three main columns of data. Column A includes city names. Column B includes hotel names. Column C includes random numbers. I am trying to have excel return the number associated with the second occurance of a given hotel in each of my cities.

Boston Days Inn 1
Boston Hyatt 2
Boston Days Inn 3
Boston Hilton 4

I want the formula to return the number *3* associated with the second Days Inn, within Boston.

So I inserted a column C (at the advice of a fellow board member who helped me yesterday!!) that connects Boston with Days Inn (=A2&B2). I also put the city name in F2 and hotel name in G2.

The formula in H2:
=VLOOKUP(F2&G2,C1:D11,2,0)

This returns *1* for the first occurance of Days Inn in Boston, which is great. However, now I want to adjust so that *3* is returned, for the second occurance. I can not re-sort the data...it has to stay as is.

Thanks for your help!!!!!

 

[Aladin Akyurek]

Hi--

I have three main columns of data. Column A includes city names. Column B includes hotel names. Column C includes random numbers. I am trying to have excel return the number associated with the second occurance of a given hotel in each of my cities.

Boston Days Inn 1
Boston Hyatt 2
Boston Days Inn 3
Boston Hilton 4

I want the formula to return the number *3* associated with the second Days Inn, within Boston.

So I inserted a column C (at the advice of a fellow board member who helped me yesterday!!) that connects Boston with Days Inn (=A2&B2). I also put the city name in F2 and hotel name in G2.

The formula in H2:
=VLOOKUP(F2&G2,C1:D11,2,0)

This returns *1* for the first occurance of Days Inn in Boston, which is great. However, now I want to adjust so that *3* is returned, for the second occurance. I can not re-sort the data...it has to stay as is.

Thanks for your help!!!!!

Book3
	A	B	C	D	E	F	G	H
1
2	Boston	Days Inn	1		Boston	Days Inn	2	3
3	Boston	Hyatt	2
4	Boston	Days Inn	3
5	Boston	Hilton	4
6
Sheet1

H2:

=INDEX($C$2:$C$5,SMALL(IF($A$2:$A$5=E2,IF($B$2:$B$5=F2,ROW($C$2:$C$5)-ROW($C$2)+1)),G2))

which must be confirmed with control+shift+enter, not just with enter.

 

[excel_training_wheels]

YES! It worked.

Thanks a MILLION.

 

[Peter_SSs]

excel_training_wheels

Just for the fun of it, here's a way without the array formula, but still with the extra column like my answer to your previous thread.

The formula in C2 (copied down) is now:
=A2&B2&SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))

The formula in I1 is:
=F2 & " "&G2&" Occurrence "&H2

The formula in I2 is now:
=VLOOKUP(F2&G2&H2,C1:D11,2,0)

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1	City	Hotel		Number		City	Hotel	Occurrence	Memphis Hyatt Occurrence 2
2	Boston	Days Inn	BostonDays Inn1	1		Memphis	Hyatt	2	4
3	Boston	Days Inn	BostonDays Inn2	2
4	Boston	Hilton	BostonHilton1	3
5	Boston	Hyatt	BostonHyatt1	4
6	Boston	Hyatt	BostonHyatt2	5
7	Memphis	Hilton	MemphisHilton1	6
8	Memphis	Hyatt	MemphisHyatt1	8
9	Memphis	Travel 1	MemphisTravel 11	9
10	Memphis	Hyatt	MemphisHyatt2	4
11	Memphis	Hyatt	MemphisHyatt3	1
City_Hotel (2)

 

[Aladin Akyurek]

excel_training_wheels

Just for the fun of it, here's a way without the array formula, but still with the extra column like my answer to your previous thread.

The formula in C2 (copied down) is now:
=A2&B2&SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))

The formula in I1 is:
=F2 & " "&G2&" Occurrence "&H2

The formula in I2 is now:
=VLOOKUP(F2&G2&H2,C1:D11,2,0)
...

An expensive fun... BTW, a SumProduct formula operates on arrays just like a formula confirmed with control+shift+enter does.