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

Peptide

Board Regular
Joined
Apr 12, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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


ABCD
1Bob ArizonaSarah
2SarahArizonaIowaJack
3Sue MaineWill
4JackIowaArizonaTom
5WillMaine
6TomArizona
7
8
9Joe ColoradoLarry
10LarryColoradoUtahSam
11SamUtahColoradoMichelle
12MichelleColoradoTexasBrad
13Tim
14BradTexas

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
ABCD
1BobArizonaSarah
2SarahArizonaIowaJack
3SueMaineWill
4JackIowaArizonaTom
5WillMaine
6TomArizona
7
8
9JoeColoradoLarry
10LarryColoradoUtahSam
11SamUtahColoradoMichelle
12MichelleColoradoTexasBrad
13Tim
14BradTexas
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.

Code:
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.
 
Upvote 0
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.

ABCDEF
1
2BobArizonaSarah27
3SarahArizonaIowaJack27
4SueMaineWill27
5JackIowaArizonaTom27
6WillMaine27
7TomArizona27
897
9108
10JoeColoradoLarry1015
11LarryColoradoUtahSam1015
12SamUtahColoradoMichelle1015
13MichelleColoradoTexasBrad1015
14Tim1015
15BradTexas1015
161715

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet19

Array Formulas
CellFormula
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)),""))}
D2{=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))))}
E2{=MAX(IF($A$1:A2="",ROW($A$1:A2)))+1}
F2{=MIN(IF(A2:A19="",ROW(A2:A19)))-1}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.
 
Upvote 0
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
 
Last edited:
Upvote 0
Since you said that the table can be in different columns, I removed some absolute references in the formulas.

ABCDEFGHIJKLMN
1
2BobArizonaSarah27
3SarahArizonaIowaJack27
4SueMaineWill27
5JackIowaArizonaTom27
6WillMaine27
7TomArizona27
897
9108
10JoeColoradoLarry1015
11LarryColoradoUtahSam1015
12SamUtahColoradoMichelle1015
13MichelleColoradoTexasBrad1015
14Tim1015
15BradTexas1015
161715HankIdahoJane1623
171816JaneIdahoOregonMary1623
18MaryOregonLouisianaLou1623
19LouLouisianaIdahoAlice1623
20ElaineCaliforniaCal1623
21AliceIdaho1623
22Belle1623
23CalCalifornia1623
24

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet19

Array Formulas
CellFormula
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)),""))}
D2{=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))))}
E2{=MAX(IF(A$1:A2="",ROW(A$1:A2)))+1}
F2{=MIN(IF(A2:A19="",ROW(A2:A19)))-1}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top