?
Solved

Separate items into a different row in Excel

Posted on 2016-08-19
13
Medium Priority
?
107 Views
Last Modified: 2016-08-23
Hello EE

Can someone assist me with this please , I need to be able to separate the items in Column B into a separate Row .
Current outputThis is what I need
0
Comment
Question by:MilesLogan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41763274
It is a 2-step process.
The first step involves converting the multi line cell to multiple columns.  This is done in native Excel and can be recorded as a macro.
Select column B
From the ribbon, select Data
From the Data menu, select Text to Columns
From the Text to Columns Wizard, select Delimited then select Next.
At Step 2 of the Wizard, uncheck all options and select Other.
In the box next to Other hold the Alt key down and type 0010.
Select Next on Step 2.
At Step 3 of the Wizard, in the Destination field, enter =$D$1 and select Finish.
Now you should have each item in column B in columns D through as many columns as are needed on the same line as the machine name.

If you record this as a macro, you can see how this is performed programmatically.
For a reference to the function, please review this link on the Microsoft MSDN site.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41763278
Or, if this is being generated via Powershell, post your script (use the CODE link and paste your script into the code block) and we might be able to change the script to provide you with exactly what you want rather than gathering the data in one place and massaging it into shape in another.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41763343
How about a sample file for testing? I do not wish to type this.
0
Q2 2017 - Latest Malware & Internet Attacks

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out our latest Quarterly Internet Security Report!

 
LVL 11

Expert Comment

by:Wilder1626
ID: 41763848
Another option could be to use the pivot table option. You can do do easily.
Pivot table no1

Here is a sample in attachment
Pivot-Table_no1.xlsx
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41763920
I believe you're using Powershell to read AD.

Likely something along the lines of

Get-ADPrincipalGroupMembership (Get-ADComputer [b]MachineName1[/b]).DistinguishedName

Open in new window

and piping it out to a CSV.

Have you tried something like this, where AllComputers.TXT is a list of the computers whose membership you want to identify.

Import-Module ActiveDirectory
Get-Content C:\Scripts\AllComputers.txt | Get-ADComputer $_ -Properties memberOf |
Select-Object -Property @{N= "Name";E= {$_.samaccountname}},@{N= "Groups";E={$_.Memberof}} |
export-csv -path C:\Data\AllComputers_results.csv -append

Open in new window

0
 
LVL 2

Author Comment

by:MilesLogan
ID: 41764471
HI WalkabouTigger when I pull the data via Powershell it is in the correct format but it takes much longer to get . This data is being generated from IBM BigFix and apparently this is the only way they can get me the data .

So I cant do this option below .
Other hold the Alt key down and type 0010.

When I press the ALT key and I try to enter 0010 it beeps , does not allow me to enter them.
0
 
LVL 2

Author Comment

by:MilesLogan
ID: 41764477
HI Wilder1626

Can you show me how you did that ?
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41764493
Please try this....

Click the button on Test Sheet in the attached to run the code to get the data in the desired format.
If you have an issue downloading and opening the file due to a temporary bug in the forum, first download and save it on your system and then open it.

Sub TransformData()
Dim lr As Long, i As Long
Dim str() As String

Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
   str() = Split(Cells(i, 2), Chr(10))
   If UBound(str) > 0 Then
      Cells(i + 1, 1).Resize(UBound(str)).EntireRow.Insert
      Cells(i, 2).Resize(UBound(str) + 1).Value = Application.Transpose(str)
   End If
Next i
lr = Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:A" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Application.ScreenUpdating = True
End Sub

Open in new window

SeparateItems.xlsm
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 41764504
MilesLogan

See this small video that will show you how to do this.

Let us know if you have any questions.
Pivot-table-No1.zip
0
 
LVL 2

Author Closing Comment

by:MilesLogan
ID: 41767026
Hi Sudohn , this worked perfect .. thank you so much
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41767032
You're welcome MilesLogan! Glad to help.
0
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 41767271
Nice solution, Subodh.

Having dealt with BigFix in the past, you have my sincerest sympathies, Miles.
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41767290
@WalkaboutTigger
Thanks for the appreciation. :)
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question