Return the next unique value in a range

[tallinex]

Hello All,
I am wanting to have a formula in cell A21, A22, A23 and A24 that will look for the string value in the cell above within the 4 week calendar in the top section (range A2:G17) and return the next value (hotel name) to basically build up a list of unique values.
For the first cell in the list i have set it to look for the first non-blank cell in the first hotel row (=INDEX(A4:G4,MATCH(TRUE,INDEX((A4:G4<>0),0),0)))

Any help is apriciated

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, note that you will attract more potential helpers (& faster help) if you post your sample data in a form that can be copied from your post to a worksheet for testing. My signature block below has a link with options.

Somebody will quite likely come up with a formula solution for you, but here is a user-defined function that you may wish to consider. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown** in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

** Note that the function has 3 arguments.
- A range that includes all the hotels names. The first row of the range must be the first row containing hotel names. The 'hotel rows' must be uniformly spaced in the range.
- The 'RowGap' being that uniform spacing of the hotel rows within the above range.
- Which item in the list to be returned. These values will be 1, 2, 3 etc as the formula is copied down.

Function NextHotel(rng As Range, RowGap As Long, ItmNum As Long) As String
  Dim d As Object
  Dim a As Variant
  Dim rw As Long, col As Long

  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = rng.Value
  For rw = 1 To UBound(a) Step RowGap
    For col = 1 To UBound(a, 2)
      If Len(a(rw, col)) > 0 Then
        d(a(rw, col)) = 1
      End If
    Next col
  Next rw
  If ItmNum <= d.Count Then NextHotel = d.keys()(ItmNum - 1)
End Function

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G
2	30/07/2017	31/07/2017	1/08/2017	2/08/2017	3/08/2017	4/08/2017	5/08/2017
3
4		Hilton Paris	Hilton Paris	Hilton Paris	Hilton Paris	Hilton Paris	Mecure Frankfurt
5
6	6/08/2017	7/08/2017	8/08/2017	9/08/2017	10/08/2017	11/08/2017	12/08/2017
7
8	Mecure Frankfurt	Mecure Frankfurt	Mecure Frankfurt	Hilton London	Hilton London	Hilton London	Hilton London
9
10	13/08/2017	14/08/2017	15/08/2017	16/08/2017	17/08/2017	18/08/2017	19/08/2017
11
12	Hilton London	Hilton London	Hilton London	Hilton London	Hilton London	Hilton London	Hilton London
13
14	20/08/2017	21/08/2017	22/08/2017	23/08/2017	24/08/2017	25/08/2017	26/08/2017
15
16
17
18
19	Hotels
20	Hilton Paris
21	Mecure Frankfurt
22	Hilton London
23
24
Hotel List

Cell Formulas
Range	Formula
A20	=NextHotel(A$4:G$16,4,ROWS(A$20:A20))

 

[Aladin Akyurek]

A picture is not Excel readable, forcing a would-be helper to retype everything in order to experiment!...

In A20 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$4:$G$4,SMALL(IF(FREQUENCY(IF(1-($A$4:$G$4=""),MATCH($A$4:$G$4,$A$4:$G$4,0)),COLUMN($A$4:$G$4)-COLUMN($A$4)+1),TRANSPOSE(COLUMN($A$4:$G$4)-COLUMN($A$4)+1)),ROWS($A$20:A20))),"")