Handling Data Multiple values in single cell in one column

Question

Hello Good Evening All,

I have a question in excel when I worked on it several hours but not final answer, achieved only little, realized I need someone help, please help me here with this issue

I have excel sheet (attached data sample), each employee has one or none or more than one technology column, I need to convert (split) that each technology value with other column values for that row please see below.,

for easy to understand I applied colors at bottom section of the image, so please help with this data breakup (technology column values split)

What I DID so far

select the column, and then Data --> text to columns then delimiter, Other , Alt+0010 it separated all of the values as separate columns then I stuck here

please kindly help me

Thank you in advance

MIlan

Answer 1

Hi,

Here's what worked for me

1. Select A2:E8, copy it and paste it in MS Word
2. Copy the data from MS Word and paste it back in the Excel workbook

The steps above will surprisingly resolve the multiple entries per cell issue.  You will now see each entry appearing in its own cell

3. Select data in all columns except the technology column and click on the Merge and Centre icon to unmerge the cells
4. Select data in all columns except the technology column and press Ctrl+G > Special > Blanks > OK
5. Press =, then up arrow and finally Ctrl+Enter
6. Select data in all columns except the technology column and press Ctrl+C.  Right click > Paste Special > Values > OK

Hope this helps.

Answer 2

Hopefully you can use a macro to get the job done.  Since we don't know how many 'technologies' may be involved on any given row, it's difficult to come up with a formula to handle the entire job.

To put the macro into your workbook:

Open the workbook.

Press [Alt]+[F11] to open the Visual Basic Editor (VBE)

Use the VBE menu to  Insert --> Module

Copy the code below and paste it into the module.

Edit the two constants that now point to columns A and E as needed.

Close the VBE using its red-x or pressing [Alt]+[F11] again.

SAVE the file as a macro enabled file, type .xlsm or .xlsb

To use the macro - select the original data sheet with the multiple-line entries for technologies.  From the [View] tab choose Macros and then "View Macros". Select the macro named MakeNewRowsOfData and [Run] it.

Examine the results on the new sheet in the workbook named Results.  The original information will not be changed at all.  Row 1 on the Results sheet will be empty, you can simply delete that row.

Here is the code:

Sub MakeNewRowsOfData()

  'assumes the following:

  '   Technologies are in column E

  '   beginning at row 2

  '   Employee IDs are in column A and

  '   there are now empty rows in column A

  '

  'This will create a new sheet named Revised

  'that will contain the revised data, and that

  'you can rename as required.  You could even

  'delete the data on the original sheet and copy

  'the data from the new one back into the original.

  '

  'The source data sheet must be selected when you

  'start this macro

  '

  '****

  'change these 2 Const values as required

  Const empIDCol = "A"

  Const technologyCol = "E"

  '************************

  Dim srcWS As Worksheet

  Dim destWS As Worksheet

  Dim srcEmpIDList As Range

  Dim anyEmpID As Range

  Dim copyRange As Range

  Dim technologies As Variant

  Dim origTechEntry As String

  Dim techCount As Integer

  Dim nextDestRow As Long

  Application.ScreenUpdating = False

  Set srcWS = ActiveSheet

  Set srcEmpIDList = srcWS.Range(empIDCol & "1:" & _

   srcWS.Range(empIDCol & Rows.Count).End(xlUp).Address)

  On Error Resume Next

  Set destWS = Worksheets("Revised")

  If Err <> 0 Then

    'Revised sheet doesn't exist,create it

    Worksheets.Add after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    Set destWS = ActiveSheet

    destWS.Name = "Revised"

    Err.Clear

  End If

  On Error GoTo 0

  destWS.Cells.Clear

  srcWS.Activate

  For Each anyEmpID In srcEmpIDList

    origTechEntry = Trim(srcWS.Range(technologyCol & anyEmpID.Row))

    technologies = Split(origTechEntry, Chr$(10))

    If origTechEntry <> "" Then

      For techCount = LBound(technologies) To UBound(technologies)

        nextDestRow = destWS.Range(empIDCol & Rows.Count).End(xlUp).Row + 1

        Set copyRange = srcWS.Rows(anyEmpID.Row & ":" & anyEmpID.Row)

        copyRange.Copy

        destWS.Cells(nextDestRow, 1).PasteSpecial xlPasteAll

        destWS.Range(technologyCol & nextDestRow) = technologies(techCount)

        Application.CutCopyMode = False

      Next ' end techCount loop

    Else

      'when no entry in column E for an original row

        nextDestRow = destWS.Range(empIDCol & Rows.Count).End(xlUp).Row + 1

        Set copyRange = srcWS.Rows(anyEmpID.Row & ":" & anyEmpID.Row)

        copyRange.Copy

        destWS.Cells(nextDestRow, empIDCol).PasteSpecial xlPasteAll

        Application.CutCopyMode = False

    End If

  Next ' end anyEmpID loop

  'cleanup and housekeeping

  'release assigned resources back to the system

  Set srcEmpIDList = Nothing

  Set srcWS = Nothing

  Set destWS = Nothing

  MsgBox "Task Completed.", vbOKOnly + vbInformation, "Job Done"

End Sub

Answer 3

Excel 2010 with free Power Query Add-In.

Compatible with Office 2013/2016 Pro Plus.

No formulas, no VBA macro.

http://www.mediafire.com/download/m3ze4d3z1c9czne/11_12_15.xlsx

Answer 4

Sir Latham Thank you very much for input I will try and let you know

God Bless You, Thanks Again

Answer 5

Hello Herbert Thank you much for the tip

May I know how can I do it, steps? please

also in your sample you did it with "," for me it is just char(10) please let me know the steps

it will Greatly helpful

Thank you again

Milan