List cells in a range into one column

mikec82

Board Regular
Joined
Jan 13, 2009
Messages
225
I have a sheet that looks like this:

FirstLastID 1ID 2ID 2
MarkSmith0005-1230005-4560005-789
JohnTurner0005-2340005-5670005-8910
MaryRichards0005-3450005-6780005-8911

<tbody>
</tbody>

I need to have one column in a second sheet that lists everything in ID 2 and ID3 so that I have this:

IDs
0005-456
0005-567
0005-678
0005-789
0005-8910
0005-8911

<tbody>
</tbody>

Is this possible? Would this be a VLOOKUP? I want to make sure that it only lists unique values - no duplicates.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Paste the below code into a Standard Module of your VBE. Then whatever Range of Cells you wish to INCLUDE in the transfer simply HIGHLIGHT them, or select then
In your case Highlight Range D2:E4. Run from this same page (Sheet1). Afterwards check your Sheet2 Column A...
Jim


Code:
Sub Foo()
Dim arr() As Variant
Dim Rng As Range, c As Range
Dim j As Integer, TCnt As Integer, i As Integer
Set Rng = Selection
TCnt = Rng.Count
For Each c In Rng
    ReDim Preserve arr(i)
    arr(i) = c
    i = i + 1
Next c
With Worksheets("Sheet2")
For j = 0 To TCnt - 1
    .Cells(j + 2, 1) = arr(j)
Next j
End With
End Sub
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you can try PowerQuery aka Get&Transform

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    ROC = Table.ToColumns(Table.SelectColumns(Source,{"ID 2", "ID 22"})),
    ToTable = Table.FromList(ROC, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandListColumn(ToTable, "Column1"),
    Rename = Table.RenameColumns(Expand,{{"Column1", "IDs"}})
in
    Rename[/SIZE]

FirstLastID 1ID 2ID 22IDs
MarkSmith0005-1230005-4560005-7890005-456
JohnTurner0005-2340005-5670005-89100005-567
MaryRichards0005-3450005-6780005-89110005-678
0005-789
0005-8910
0005-8911
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,622
Office Version
  1. 2007
Platform
  1. Windows
Try this, change "Data" and "ID" by the names of your sheets.

Code:
Sub Macro1()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]ID[/COLOR]")
    lr = sh1.Range("D" & Rows.Count).End(xlUp).Row
    sh2.Range("A2").Resize(lr - 1).Value = sh1.Range("D2:D" & lr).Value
    sh2.Range("A" & lr + 1).Resize(lr - 1).Value = sh1.Range("E2:E" & lr).Value
    sh2.Range("A2:A" & lr * 2 - 1).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,993
Messages
5,639,423
Members
417,089
Latest member
jonstr101

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
Top