Clever Geek Handbook

Combine values ​​in multiple columns into one

I have the following data structure:

Data + Expected Results + What I get

As you can see in column J , I try to combine the data in one column of the columns A and C , and E . > And the G .

I am using this formula: 

=IF(ROW()<=COUNTA($A:$A);INDEX($A:$C;ROW();COLUMN(A1));INDEX($A:$C;ROW()-COUNTA($A:$A)+1;COLUMN(C1)))

and I am getting the values ​​in column K as you can see. Currently, this formula only combines two columns. How do I change it to combine all four columns?


And how do I get just those values ​​starting at line 5 ?
The column height will change constantly: sometimes there are 10 values ​​in column A and sometimes there are 2 values.


Any excel formula or any VBA code will be acceptable.

+3


source to share


3 answers


There is a fairly standard method for getting unique values ​​from a column, but not for multiple columns. To get an extract from multiple columns, you need to compose multiple formulas, along with processing passed to sequential columns, one of which is an earlier formula error.

Collect Unique from multiple columns

Array formula ¹ in J5 is equal to

=IFERROR(INDEX($A$5:$A$99, MATCH(0, IF(LEN($A$5:$A$99), COUNTIF(J$4:J4, $A$5:$A$99), 1), 0)),
 IFERROR(INDEX($C$5:$C$99, MATCH(0, IF(LEN($C$5:$C$99), COUNTIF(J$4:J4, $C$5:$C$99), 1), 0)),
 IFERROR(INDEX($E$5:$E$99, MATCH(0, IF(LEN($E$5:$E$99), COUNTIF(J$4:J4, $E$5:$E$99), 1), 0)),
 IFERROR(INDEX($G$5:$G$99, MATCH(0, IF(LEN($G$5:$G$99), COUNTIF(J$4:J4, $G$5:$G$99), 1), 0)),
 ""))))


I've only included columns A, C, E, and G since your sample data only shows duplicates in columns B, D, F, and H.

¹ Array formulas must be completed with Ctrl+ Shift+ Enter↵. If entered correctly, Excel encloses the formula in curly braces (for example, { and } ). You don't type curly braces in yourself. Once entered correctly in the first cell, they can be filled in or copied or right just like any other formula. Try and reduce full column references to ranges that represent the extents of your actual data in more detail. Array formulas break logarithmically calculated cycles, so it is recommended to keep the narrowed reference ranges to a minimum. See Guidelines and Examples of Array Formulas for details .

+7


source


This answer is just another way of thinking about formulas that you could use for this kind of problem. Comes to the point of @Jeep that it's hard to find unique values ​​across multiple columns. My first step is to create a single column.

If you can live with a helper column, these formulas may be slightly easier to maintain than the suggested nested one IFERROR

. They are equally difficult to understand, though at first glance. Another potential is that it scales well as the number of columns increases.

You can use CHOOSE

some math INDEX

to build a separate column array of a split column group. The trick is to CHOOSE

connect discontinuous ranges side-by-side when given an array as a selection parameter. If it starts with columns of the same size, you can use division math and math to turn it into a single column.

The range display displays four groups of data with overlapping red reds.

picture of ranges

The formula in the F2:F31

- it's an array formula. It is concatenation of all columns into an array and then back into one column. I selected the columns out of order to emphasize that it handles discontinuous range.



=INDEX(CHOOSE({1,2,3,4}, A2:A7,C2:C7,B2:B7,D2:D7), MOD(ROW(1:30)-1, ROWS(A2:A7))+1,INT((ROW(1:30)-1)/ROWS(A2:A7))+1)

The array formula at H2

and copied down is the standard formula for unique values. The only exception is that instead of avoiding whitespace as usual, I am avoiding 0 values.

=IFERROR(INDEX(F2:F31,MATCH(0,IF(F2:F31=0,1,COUNTIF($H$1:H1,F2:F31)),0)),"")

A few other comments about this approach:

  • As CHOOSE

    I use {1,2,3,4}

    . This can be replaced with TRANSPOSE(ROWS(1:4))

    or any number of columns you have.
  • There are also two places ROWS(A2:A7)

    , it can only be 2:7

    or 1:6

    or any size used for the column size. I used one of the data ranges to make it easier to color and emphasize that it should match the block size.
  • And it is ROW(1:30)

    used for the number of common items to collect. It really should be 1:24

    as there are elements 6*4

    , but I made it big when testing.

There are definitely a few downsides to this approach, but it can be a good toolbox storage trick. Never know when you want to make a discontinuous range column. The biggest drawback is that the data columns must be the same size (and of course the helper column).

+2


source


This code will do what you ask:

Sub MoveData()

START_ROW = 5
START_COL = 1
STEP_COL = 2
OUTPUT_ROW = 5
OUTPUT_COL = 10

Row = START_ROW
Col = START_COL
Out_Row = OUTPUT_ROW
While Col < OUTPUT_COL
    While Cells(Row, Col).Value <> ""
        Cells(Out_Row, OUTPUT_COL).Value = Cells(Row, Col).Value
        Out_Row = Out_Row + 1
        Row = Row + 1
    Wend
    Row = START_ROW
    Col = Col + STEP_COL
Wend

End Sub

+1


source






More articles:


All Articles