Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have two columns, A and B. Each column contains a list of data in
text format e.g. 01TI518A.PV . It is possible that duplicates of this text may appear within either column. Also, the number of data values in each column is variable as they are imported from an external source into the worksheet. I would like to be able to combine the text from both columns into a single list in a single column, say column C. I would also like this column to be alphanumerically sorted. How do I go about doing this? |
#2
![]() |
|||
|
|||
![]()
Sure, I can help you with that!
Here are the steps to combine the data in columns A and B into a single column C and sort it alphabetically:
That's it! Now you should have a combined list of data from columns A and B in column C, sorted alphabetically. Let me know if you have any questions or if there's anything else I can help you with.
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Check your other post.
Tommy wrote: I have two columns, A and B. Each column contains a list of data in text format e.g. 01TI518A.PV . It is possible that duplicates of this text may appear within either column. Also, the number of data values in each column is variable as they are imported from an external source into the worksheet. I would like to be able to combine the text from both columns into a single list in a single column, say column C. I would also like this column to be alphanumerically sorted. How do I go about doing this? -- Dave Peterson |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Select all of the entries in column A, choose Edit | Copy. Click in cell C1
and use Edit | Paste to paste them into column C. Select the entries in column B, again use Edit | Copy and go to the cell below the last entry in column C, click it and use Edit | Paste to add those to the list. Click the 'C' to choose all of column C and either use the A|Z icon to sort them or use Data | Sort and when asked if you wish to expand the area, reply [No]. At this point you have both columns in one and they're sorted. Duplicate entries will be grouped together within the column. If you need to identify those, you can put this formula in column D at D1: =IF(COUNTIF(C:C,C1)1,"Duplicate","") fill the formula to the end of your list. Or were you wanting a macro solution? "Tommy" wrote: I have two columns, A and B. Each column contains a list of data in text format e.g. 01TI518A.PV . It is possible that duplicates of this text may appear within either column. Also, the number of data values in each column is variable as they are imported from an external source into the worksheet. I would like to be able to combine the text from both columns into a single list in a single column, say column C. I would also like this column to be alphanumerically sorted. How do I go about doing this? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If you wanted a macro to do this, then here is one that will do it. To put
it into the workbook. Press [Alt]+[F11] to open the VB Editor, then choose Insert | Module from the VBE menu. Copy the code below and paste it into the code module. Close the VB Editor and to run it, choose Tools | Macro | Macros, select the macro name and click the [Run] button. It assumes empty columns in C and D to begin with. Sub CombineColumns() Dim rngToCopy As Range Dim destRange As Range Dim lastRow As Long lastRow = Range("A" & Rows.Count).End(xlUp).Row Set rngToCopy = Range("A1:A" & lastRow) Set destRange = Range("C1:C" & lastRow) destRange.Value = rngToCopy.Value lastRow = Range("B" & Rows.Count).End(xlUp).Row Set rngToCopy = Range("B1:B" & lastRow) lastRow = Range("C" & Rows.Count).End(xlUp).Row Set destRange = Range("C" & lastRow + 1 & ":C" & _ lastRow + rngToCopy.Rows.Count) destRange.Value = rngToCopy.Value Set rngToCopy = Nothing lastRow = Range("C" & Rows.Count).End(xlUp).Row Set destRange = Range("C1:C" & lastRow) destRange.Sort Key1:=Range("C1"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Set destRange = Range("D1:D" & lastRow) destRange.FormulaR1C1 = "=IF(COUNTIF(C[-1],RC[-1])1," _ & Chr$(34) & "Duplicate" & Chr$(34) & "," & _ String(2, 34) & ")" Set destRange = Nothing End Sub "Tommy" wrote: I have two columns, A and B. Each column contains a list of data in text format e.g. 01TI518A.PV . It is possible that duplicates of this text may appear within either column. Also, the number of data values in each column is variable as they are imported from an external source into the worksheet. I would like to be able to combine the text from both columns into a single list in a single column, say column C. I would also like this column to be alphanumerically sorted. How do I go about doing this? |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Arrrggghhhh! Not to you, Dave, but to Tommy: my responses HERE emphasize why
posting in multiple places causes wasted time and effort. "Dave Peterson" wrote: Check your other post. Tommy wrote: I have two columns, A and B. Each column contains a list of data in text format e.g. 01TI518A.PV . It is possible that duplicates of this text may appear within either column. Also, the number of data values in each column is variable as they are imported from an external source into the worksheet. I would like to be able to combine the text from both columns into a single list in a single column, say column C. I would also like this column to be alphanumerically sorted. How do I go about doing this? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How can I combine multiple columns of data into 1 column? | New Users to Excel | |||
Combine the data in 2 columns of 20 rows into one column of 40 row | Excel Discussion (Misc queries) | |||
How to combine data from two columns into one column? | Excel Worksheet Functions | |||
How do I sort a column alphabetically not consider "the" or "a"? | Excel Discussion (Misc queries) | |||
How do I combine tabulated data into a single column and list alphabetically? | Excel Discussion (Misc queries) |