2 columns of data, find non blanks, list adjacent cell data

[Peptide]

Hello -
I have several grids on the same worksheet (2 are below) so I cant use entire columns in the formula
I need to be able to define specific data ranges
Columns A & B are the data and will contain duplicates which is needed
Looking for formulas

Need
Starting in C1, list all non blank values from Column B (I would like to exclude all blanks from the list)
- If C can be in alphabetical order great, if not, not critical)
Starting in D1, list the exact adjacent value from Column A

Logic:
C1 starts with Arizona (from B2), D1 then states what was in adjacent A2 (NOT A6)
C2 continues with Iowa (from B4), D2 then states what was in A4


[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"] [/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]1[/TD]
[TD="class: xl68, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl68, width: 64"]Arizona[/TD]
[TD="class: xl68, width: 64"]Sarah[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]2[/TD]
[TD="class: xl68, width: 64"]Sarah[/TD]
[TD="class: xl68, width: 64"]Arizona[/TD]
[TD="class: xl68, width: 64"]Iowa[/TD]
[TD="class: xl68, width: 64"]Jack[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]3[/TD]
[TD="class: xl68, width: 64"]Sue[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl68, width: 64"]Maine[/TD]
[TD="class: xl68, width: 64"]Will[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]4[/TD]
[TD="class: xl68, width: 64"]Jack[/TD]
[TD="class: xl68, width: 64"]Iowa[/TD]
[TD="class: xl68, width: 64"]Arizona[/TD]
[TD="class: xl68, width: 64"]Tom[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]5[/TD]
[TD="class: xl68, width: 64"]Will[/TD]
[TD="class: xl68, width: 64"]Maine[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]6[/TD]
[TD="class: xl68, width: 64"]Tom[/TD]
[TD="class: xl68, width: 64"]Arizona[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]7[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]8[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]9[/TD]
[TD="class: xl68, width: 64"]Joe[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl68, width: 64"]Colorado[/TD]
[TD="class: xl68, width: 64"]Larry[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]10[/TD]
[TD="class: xl68, width: 64"]Larry[/TD]
[TD="class: xl68, width: 64"]Colorado[/TD]
[TD="class: xl68, width: 64"]Utah[/TD]
[TD="class: xl68, width: 64"]Sam[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]11[/TD]
[TD="class: xl68, width: 64"]Sam[/TD]
[TD="class: xl68, width: 64"]Utah[/TD]
[TD="class: xl68, width: 64"]Colorado[/TD]
[TD="class: xl68, width: 64"]Michelle[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]12[/TD]
[TD="class: xl68, width: 64"]Michelle[/TD]
[TD="class: xl68, width: 64"]Colorado[/TD]
[TD="class: xl68, width: 64"]Texas[/TD]
[TD="class: xl68, width: 64"]Brad[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]13[/TD]
[TD="class: xl68, width: 64"]Tim[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64"]14[/TD]
[TD="class: xl68, width: 64"]Brad[/TD]
[TD="class: xl68, width: 64"]Texas[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Peptide,

You did ask for a formula solution, but, none of the formula Guru's have responded.

Here is a macro solution for you to consider, that will run in the Active Worksheet.

Sample raw data, and, results:

Excel 2007
	A	B	C	D
1	Bob		Arizona	Sarah
2	Sarah	Arizona	Iowa	Jack
3	Sue		Maine	Will
4	Jack	Iowa	Arizona	Tom
5	Will	Maine
6	Tom	Arizona
7
8
9	Joe		Colorado	Larry
10	Larry	Colorado	Utah	Sam
11	Sam	Utah	Colorado	Michelle
12	Michelle	Colorado	Texas	Brad
13	Tim
14	Brad	Texas
15
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub c()
' hiker95, 06/08/2016, ME946030
Dim Area As Range, sr As Long, er As Long, nr As Long, r As Long
Application.ScreenUpdating = False
With ActiveSheet
  .Columns("C:D").ClearContents
  For Each Area In .Range("A1", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      er = sr + .Rows.Count - 1
      nr = sr
      For r = sr To er Step 1
        If Not Range("B" & r) = vbEmpty Then
          Range("C" & nr).Value = Range("B" & r).Value
          Range("D" & nr).Value = Range("A" & r).Value
          nr = nr + 1
        End If
      Next r
    End With
  Next Area
  .Columns("C:D").AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[Eric W]

That's a VERY complicated set of requirements! I managed to find a set of equations that work, although not exactly as requested. Also, since they are array formulas with OFFSET functions, they will probably slow down your sheet if you have a lot of them.

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Bob[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"]Sarah[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Sarah[/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"]Iowa[/TD]
[TD="bgcolor: #FFFFFF"]Jack[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Sue[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Maine[/TD]
[TD="bgcolor: #FFFFFF"]Will[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]Jack[/TD]
[TD="bgcolor: #FFFFFF"]Iowa[/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"]Tom[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]Will[/TD]
[TD="bgcolor: #FFFFFF"]Maine[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]Tom[/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]Joe[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Larry[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]Larry[/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Utah[/TD]
[TD="bgcolor: #FFFFFF"]Sam[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]Sam[/TD]
[TD="bgcolor: #FFFFFF"]Utah[/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Michelle[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]Michelle[/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Texas[/TD]
[TD="bgcolor: #FFFFFF"]Brad[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]Tim[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]Brad[/TD]
[TD="bgcolor: #FFFFFF"]Texas[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]17[/TD]
[TD="align: right"]15[/TD]

</tbody>

Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IF($A2="","",IFERROR(INDEX($B:$B,SMALL(IF(OFFSET($B$1,E2-1,0,F2-E2+1,1)<>"",ROW(OFFSET($B$1,E2-1,0,F2-E2+1))),ROW()-E2+1)),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF($C2="","",INDEX(A:A,SMALL(IF(OFFSET($B$1,E2-1,0,F2-E2+1,1)=C2,ROW(OFFSET($B$1,E2-1,0,F2-E2+1))),COUNTIF(OFFSET($C$1,E2-1,0):$C2,C2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=MAX(IF($A$1:A2="",ROW($A$1:A2)))+1}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=MIN(IF(A2:A19="",ROW(A2:A19)))-1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



To start, I added a blank row on top. I wanted to allow for headings, but it turns out you can't put anything in A1. I can tweak the formulas, so that you don't need the blank first row, or to allow headings, but the formulas would have to be specific for your desires, they can't do both. Columns E and F are formulas that find the top and bottom rows of each section. The sections are defined by column A. Any empty line in column A will start a new section. These formulas theoretically could be incorporated in the C/D formulas, but that would make them horrendously long and much slower. You can hide columns E/F if you want.

C/D find the lists you want. Sorry, sorting them with formulas is a bit beyond me!

So enter C2:F2, all array formulas. Then copy C2:F2 and paste down the column.

Hope this helps.

 

[Peptide]

Thanks Eric - I think maybe I can make it easier? The two grids are not related to each other and can also be in different cells/rows. The reason I showed it that way is because many times more difficult formulas seem to have to start in column A or Row 1 for some reason. Does that make things easier? My real issue is that I need each grid to run independently. For example, the 2nd grid starting in A9, could be in N44 and has no relevancy to the 1st grid in A1.

Thoughts?

Bob

 

[Eric W]

Since you said that the table can be in different columns, I removed some absolute references in the formulas.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Hank
Jane	Idaho
Mary	Oregon
Lou	Louisiana
Elaine
Alice	Idaho
Belle
Cal	California

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Bob[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"]Sarah[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Sarah[/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"]Iowa[/TD]
[TD="bgcolor: #FFFFFF"]Jack[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Sue[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Maine[/TD]
[TD="bgcolor: #FFFFFF"]Will[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]Jack[/TD]
[TD="bgcolor: #FFFFFF"]Iowa[/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"]Tom[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]Will[/TD]
[TD="bgcolor: #FFFFFF"]Maine[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]Tom[/TD]
[TD="bgcolor: #FFFFFF"]Arizona[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]Joe[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Larry[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]Larry[/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Utah[/TD]
[TD="bgcolor: #FFFFFF"]Sam[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]Sam[/TD]
[TD="bgcolor: #FFFFFF"]Utah[/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Michelle[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]Michelle[/TD]
[TD="bgcolor: #FFFFFF"]Colorado[/TD]
[TD="bgcolor: #FFFFFF"]Texas[/TD]
[TD="bgcolor: #FFFFFF"]Brad[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]Tim[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]Brad[/TD]
[TD="bgcolor: #FFFFFF"]Texas[/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Idaho[/TD]
[TD="bgcolor: #FFFFFF"]Jane[/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]18[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFFFF"]Oregon[/TD]
[TD="bgcolor: #FFFFFF"]Mary[/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFFFF"]Louisiana[/TD]
[TD="bgcolor: #FFFFFF"]Lou[/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFFFF"]Idaho[/TD]
[TD="bgcolor: #FFFFFF"]Alice[/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]California[/TD]
[TD="bgcolor: #FFFFFF"]Cal[/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]16[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IF(A2="","",IFERROR(INDEX(B:B,SMALL(IF(OFFSET(B$1,E2-1,0,F2-E2+1,1)<>"",ROW(OFFSET(B$1,E2-1,0,F2-E2+1))),ROW()-E2+1)),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(C2="","",INDEX(A:A,SMALL(IF(OFFSET(B$1,E2-1,0,F2-E2+1,1)=C2,ROW(OFFSET(B$1,E2-1,0,F2-E2+1))),COUNTIF(OFFSET(C$1,E2-1,0):C2,C2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=MAX(IF(A$1:A2="",ROW(A$1:A2)))+1}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=MIN(IF(A2:A19="",ROW(A2:A19)))-1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



For example, I created a similar table in I16:J23. Then to get the list, I copied cells C2:F2 and pasted that to K16:N23.

One other thought, the F2 formula assumes that any list will have at most 18 entries. If it's more than that, raise the 19 to something appropriate.

 

[Peptide]

Eric -
Can all the formulas have 100% absolute references? The issue is I have data all over the spreadsheets and things like the B:B in C2 formula or the J$1 or J:J cause issues. Make sense?

C2
=IF(A2="","",IFERROR(INDEX(B:B,SMALL(IF(OFFSET(B$1,E2-1,0,F2-E2+1,1)<>"",ROW(OFFSET(B$1,E2-1,0,F2-E2+1))),ROW()-E2+1)),""))

K16
=IF(I16="","",IFERROR(INDEX(J:J,SMALL(IF(OFFSET(J$1,M16-1,0,N16-M16+1,1)<>"",ROW(OFFSET(J$1,M16-1,0,N16-M16+1))),ROW()-M16+1)),""))

On your comment about F2 going from 18 to 19 or more, to make it an absolute range, should the A2:A15 really be A2:A7?

 

[Eric W]

Have you tried the formulas? No, the formulas cannot have 100% absolute references. Some of the row numbers must be allowed to change for the formulas to work, and some have to be fixed. If you want absolute references for the columns, yes you can do that, but you'd have to manually change the formula every time you copy it to another location. I tried to design the formulas so that you wouldn't have to do that. As long as you have a blank row before and after each section, and you just copy the formulas to the rows in that section, then other data in the column should not matter.

On the F2 formula, again I tried to make the formula easy to paste to another location. You could make the range be A2:A7, but if you copied it to another location that has 10 rows, it would fail, or you'd have to manually change the range again. That formula just looks down the range (which, if you paste it, will change to a different range of the same length) and finds the first empty cell. If the first empty cell is 5 rows down, it doesn't matter if the upper limit is 5, or 18, or 10000. It will still work, but it will do a lot of extra calculations that will slow down your sheet. So if you make the length of the range to be the length of the maximum section you'd expect, then you'd never have to change the formula. There are potentially "better" ways of finding the next empty cell, but they rely on how your sheet is organized. There's a very easy way if you can guarantee that there is nothing at all in the column after your section.

I hope it makes a little more sense now.

 

[Peptide]

Eric- It makes perfect sense and it works great. I had never seen that before and you always teach me something new that I can use again. Much appreciated. Have a great weekend.

 

[Eric W]

You're welcome. Enjoy your weekend too.