Table to column Excel

Question

I have multiple sheets in excel that all have a "Name" column. I want a list with all of these combined, without duplicates. A pivot-table cannot do it from my understanding, and when trying this http://www.cpearson.com/EXCEL/TableToColumn.aspx, I only get results from column A:A. The example however, suggest that A:A, B:B, C:C should be combined in D:D when I name D:D "ColumnData" and the table "DataTable" (the naming doesn't seem to be wrong, since I get some result).

Any help? This is the code I use and have put in D2 (just changed commas to semicolons for the local of my Excel):

    =OFFSET(DataTable;
MOD(ROW()-ROW([ColumnData]);ROWS(DataTable));
TRUNC((ROW()-ROW([ColumnData]))/ROWS(DataTable));1;1)

Additional info:

First I have pivot-tables to make a list without duplicate names from my sheets (as they consist of a lot of data). Then I have a name for it (name manager) with the formula:

='Name lists'!$A$2:INDEX('Name lists'!$A:$A;MATCH("*";'Name lists'!$A:$A;-1);1)

..and call that "NamesSheet1" for example.

I then populate A:A in my table with:

=IFERROR(INDEX(NamesSheet1;MATCH(NamesSheet1;NamesSheet1;0));"")

..and drag that formula down to more rows than there are names. Then I repeat for each new column, B:B, C:C.

Is this one time? Could you just copy all data into a new column and then use the Data...remove duplicates option for that range? — xQbert
Unfortunately not. I enter new data into the sheets multiple times per month and new names are added all of the time, though all names are not in all sheets as they represent different data. I then want to have a data-validation list in my final sheet, where I can select any of the names only one time, and have formulas refer to that one cell (the list), in orde to present data from all sheets at once, where the name matches. So I just need a dynamic way to get all the names in one column for the data-validation list so I just have to enter raw data, not make a new list all the time manually. — Streching my competence
stackoverflow.com/questions/29995903/… may be what you're after then — xQbert
The end results is the same, though I browsed that topic earlier and couldn't get my head around it at all actually. The example I linked is very neat, and it claims to offer what I need, so what I need now is to understand what I've done wrong, so that I can use that solution. — Streching my competence

Apurv Pawar Apurv Pawar · Accepted Answer · 2017-05-11T15:10:06

Rename a new tab in your workbook as Master. and run the below code.It will copy all the data in different tabs to Master tab. let me know if this is what was required or any other changes required.

Sub ttest()

Dim wb As ThisWorkbook
Dim ws As Object

For Each ws In ActiveWorkbook.Worksheets
ws.Select
ActiveSheet.UsedRange.Copy
Worksheets("Master").Select
i = Range("A65536").End(xlUp).Row
Range("A" & i+1).Select
ActiveSheet.Paste
Next


End Sub

Table to column Excel

2 Answers