• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine data from several columns into one

caladanbrood

caladanbrood

Member
Hi all,

Hoping for your help again! I have three columns of data, and I want to consolidate these into a single column, removing any blanks.

The issue is that the data in the three columns is of varying length, depending on the inputs.

I have attached a sample file with the information I need to sort in A:C, and the optimal outcome starting in E5.

Thanks!
 

Attachments

  • Tasks.xlsx
    9.5 KB · Views: 28
Hey Caladanbrood

Those Blank cells are not blank cells at all, SpecialCells Blanks will not pick them up. Which makes it a slightly longer procedure. Go to Sheet2 and put a header, Test in A1.

Now run this puppy from Sheet1.

Code: 
Option Explicit

Sub testo()
Dim i As Integer
Dim sh As Worksheet
Set sh = Sheet2

    For i = 1 To 3
        Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Copy sh.Range("A65536").End(xlUp)(2)
    Next i
sh.Range("A2", sh.Range("A65536").End(xlUp)).Sort sh.[A2], xlAscending
sh.Range("A1", sh.Range("A65536").End(xlUp)).AutoFilter 1, "="
sh.Range("A2", sh.Range("A65536").End(xlUp)).SpecialCells(12).EntireRow.Delete
sh.[a1].AutoFilter
End Sub

Take care

Smallman
 
Hi Smallman,

Thanks for this, it looks pretty good.

However I notice that it is sorting the numbers in an odd way - it would be best if the clause numbers stay in the order they are already in, but this is sorting them so that all the short numbers (4.1, 5.1 etc) above all of the longer clauses (4.2.1, 4.2.2 etc)

I would rather not use VBA if I can help it - my office corporate policy disables macros by default, and this sheet will be used by a large number of people as a template - I don't really want to be explaining to each individual how to get around the controls. I would much prefer an equation method.

Thanks,
cb
 
Hi, caladanbrood!
I'd replace these:
Range("A65536")
by this:
Range.Cells(Sh.Rows.Count,1)
Despite that in certain cases it'd work fine (if you don't use more tan 64K rows), it's more correct in my opinion.
Regards!
 
Hi Caladanbrood

You can ignore the previous posters comments as they do not address your concerns in the slightest. So You are after a formula based (non vba) solution to sort and remove the information which is not quite blank. There is a large team of cowboys on this site who love a formula challenge and you are in safe hands.

Take care

Smallman
 
Hi, Caladanbrood!
You shouldn't ignore them, but if the author feels his opus being impaired, everything's fine for me. :)
I just take these last ones as of the source where they come from.
Regards!
 
Last edited:
Once again not helping the person solve their problem. Another superfluous post, not the behaviour of a measured moderator.
 
Hi, caladanbrood!

Give a look at the uploaded file.

It uses a few named ranges:

a) InputList1/2/3: fixed, as your input has emtpy cells, columns A:C
=Sheet1!$A$2:$A$100

b) Output1/2/3: dynamic, equal to input without empty cells, columns G:I
=DESREF(Sheet1!$G$1;;;CONTAR.SI(InputList1;"> ")+CONTAR.SI(InputList1;">0");1) -----> in english: =OFFSET(Sheet1!$G$1,,,COUNT.IF(InputList1,"> ")+COUNT.IF(InputList1,">0"),1)

c) OutputX12: dynamic, concatenation of Output1 and Output2 to perform your "nice" sort, column J
=DESREF(Sheet1!$J$1;;;FILAS(OutputList1)+FILAS(OutputList2);1) -----> in english: =OFFSET(Sheet1!$J$1,,,ROWS(OutputList1)+ROWS(OutputList2),1)

d) OutputX12_Num: dynamic, Output12X cleaned from dots & commas and padded with trailing zeros for your "nice" sort and converted to numbers, column K
=DESREF(OutputListX12;;1) -----> in english: =OFFSET(OutputListX12,,1)

e) OutputX12_Rank: dynamic, rank of Output12X_Num, column L
=DESREF(OutputListX12;;2) -----> in english: =OFFSET(OutputListX12,,2)

f) OutputX12_Sort: dynamic, rank of Output12X_Num, column M
=DESREF(OutputListX12;;3) -----> in english: =OFFSET(OutputListX12,,3)

g) OutputX123: dynamic, concatenation of OutputX12_Sort and Output3, column N
=DESREF(Sheet1!$N$1;;;FILAS(OutputListX12)+FILAS(OutputList3);1) -----> in english: =OFFSET(Sheet1!$N$1,,,ROWS(OutputListX12)+ROWS(OutputList3),1)

h) OutputListY: dynamic, concatenation of title, row headers and OutputX123, column P
=DESREF(Sheet1!$P2;;;3+FILAS(OutputListX123);1) -----> in english: =OFFSET(Sheet1!$P2,,,3+ROWS(OutputListX123),1)

The formulas are:

b) G1: =SI.ERROR(INDICE(InputList1;K.ESIMO.MENOR(SI(LARGO(InputList1)>0;FILA(DESREF(InputList1;-1;;));"");FILA(A1)));"") -----> in english: =IFERROR(INDEX(InputList1,SMALL(IF(LEN(InputList1)>0,ROW(OFFSET(InputList1,-1,,)),""),ROW(A1))),"")
H1: idem with InputList2
I1: idem with InputList3
These are array formulas (Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter).
Copy down as required (in the sample file up to row 100, in the actual file, up to the sum of non-empty cells of all 3 input columns).

c) J1: =SI(FILA()<=FILAS(OutputList1);INDICE(OutputList1;FILA());SI(FILA()<=FILAS(OutputList1)+FILAS(OutputList2);INDICE(OutputList2;FILA()-FILAS(OutputList1));"")) -----> in english: =IF(ROW()<=ROWS(OutputList1),INDEX(OutputList1,ROW()),IF(ROW()<=ROWS(OutputList1)+ROWS(OutputList2),INDEX(OutputList2,ROW()-ROWS(OutputList1)),""))

d) K1: =SI(FILA()<=FILAS(OutputListX12);VALOR(IZQUIERDA(SUSTITUIR(SUSTITUIR(J1;",";"");".";"")&REPETIR("0";10);10));"") -----> in english: =IF(ROW()<=ROWS(OutputListX12),VALUE(LEFT(SUBSTITUTE(SUBSTITUTE(J1,",",""),".","")&REPT("0",10),10)),"")
The nested SUBSTITUTE functions can be omitted if input at columns A:B is coherent and accordingly to regional configuration settings' digit separator (i.e., numeric data).

e) L1: =SI(FILA()<=FILAS(OutputListX12);JERARQUIA.EQV(K1;OutputListX12_Num;1);"") -----> in english: =IF(ROW()<=ROWS(OutputListX12),RANK.EQ(K1,OutputListX12_Num,1),"")

f) M1: =SI(FILA()<=FILAS(OutputListX12);INDICE(OutputListX12;COINCIDIR(FILA();OutputListX12_Rank;0));"") -----> in english: =IF(ROW()<=ROWS(OutputListX12),INDEX(OutputListX12,MATCH(ROW(),OutputListX12_Rank,0)),"")

g) N1: =SI(FILA()<=FILAS(OutputListX12);INDICE(OutputListX12_Sort;FILA());SI(FILA()<=FILAS(OutputListX12)+FILAS(OutputList3);INDICE(OutputList3;FILA()-FILAS(OutputListX12));"")) -----> in english: =IF(ROW()<=ROWS(OutputListX12),INDEX(OutputListX12_Sort,ROW()),IF(ROW()<=ROWS(OutputListX12)+ROWS(OutputList3),INDEX(OutputList3,ROW()-ROWS(OutputListX12)),""))

h) P1: Tasks
P2: ="All "&A1
P3: ="All "&B1
P4: ="All "&C1
P5: =SI(FILA()-4<=FILAS(OutputListX123);INDICE(OutputListX123;FILA()-4;1);"") -----> in english: =IF(ROW()-4<=ROWS(OutputListX123),INDEX(OutputListX123,ROW()-4,1),"")

Copy down J1:N1 & P5 down as required (row 100).

Just advise if any issue.

Regards!
 

Attachments

  • Combine data from several columns into one - Tasks (for caladanbrood at chandoo.org).xlsx
    24.7 KB · Views: 23
I'll be honest..... getting that to work gave me a horrible headache.

In the end I went without named ranges, and used helper columns to refer to each term by sequential numbers, then did an index/match lookup to return each number in order.

However some of the stuff you used came in very helpful on another part of the sheet, so thank you SirJB7!! I appreciate the time you must have spent going through all of that for me :)
 
Last edited:
Hi, calandanbrood!

In cases like this maybe the inverse process is easier: take the uploaded file as a base, keep its structure, place your data into it, if it still works (it should), then rearrange the data to fit your requirements.

However, glad to help you. Thanks for the feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top