Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Data segregation

Posted on 2013-01-14
Medium Priority
461 Views
Hi,

I have a formula / macro written which will actually separates the data and will fill in the appropriate buckets. The below line is copied to ‘Copied’ worksheet in ‘PAC Standard.xlsm’ workbook. Once copied, the formula in ‘Sorted’ worksheet will search for the required data and will put in the appropriate bucket.  This works really fine when there is only one serial number in the given line. Please refer to ‘PAC Standard v1.1 one line only.xlsm’ workbook for better understanding.

Example of a line with one serial number:

Serial Number :PSQS Dummy S/N;Host Name:Pillar Bricks Integration;Instance Id :60091571;

However, if the line has multiple serial numbers then this formula is not effective. Kindly note that each line will start with a serial number and the data associated with it.

Example of a line with multiple serial numbers:

I have given an example on how the data should separate once it is copied in ‘copied’ worksheet of ‘PAC Standard v1.1 multiple lines.xlsm’. Please review and kindly do the needful. Your help is highly appreciated.

Thank you,
Prashanth
PAC-Standard-v1.1-one-line-only.xlsm
PAC-Standard-v1.1-multiple-lines.xlsm
0
Question by:pg1533
[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

LVL 24

Accepted Solution

Steve earned 2000 total points
ID: 38776355
The attached file will extract the  data in cells that are A1 sequencially down A2 A3 etc.

The code is below...
Note that only data with a valid matching header will output, other data will be ignored.

``````Sub Extract_from_copied()

Dim arr, arr2
Dim nextdatarow As Long
Dim x As Long, y As Long, i As Long
i = 1
Do While Len(Sheets("Copied").Range("A" & i).Value) <> 0
arr = Sheets("Copied").Range("A" & i).Value

arr = Replace(arr, "Serial Number :", "|")
arr = Split(arr, "|")

For x = 1 To UBound(arr)
arr2 = Split(arr(x), ";")

nextdatarow = Sheets("Sorted").Range("B" & Sheets("Sorted").Rows.Count).End(xlUp).Row + 1

Sheets("Sorted").Cells(nextdatarow, "B").Value = arr2(0)
For y = 1 To UBound(arr2)
If Len(Trim(arr2(y))) <> 0 Then
ValMatch = Trim(Left(arr2(y), InStr(1, arr2(y), ":") - 1))
ValReturn = Trim(Right(arr2(y), Len(arr2(y)) - (InStr(1, arr2(y), ":"))))
On Error Resume Next
Sheets("Sorted").Cells(nextdatarow, WorksheetFunction.Match(ValMatch, Sheets("Sorted").Range("A1:J1"), 0)).Value = ValReturn
On Error GoTo 0
End If
Next y

Next x
i = i + 1
Loop
End Sub
``````
PAC-Standard-v1.1-multiple-lines.xlsm
0

Author Comment

ID: 38776466
Hi Barman,

Really appreciate your assistance on this. It is working as requested. Thank you so much for your time and support on this.

Have a great day!!!

Regards,
Prashanth
0

## Featured Post

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month9 days, 3 hours left to enroll