Solved

Data segregation

Posted on 2013-01-14
2
442 Views
Last Modified: 2013-01-14
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:

Serial Number :PSQS Dummy S/N;Host Name:Pillar Bricks Integration;Instance Id :60091571;Serial Number :PX60909015;Address Line 1:KARL SCHWEIGHOFERGASSE 3;Address Line 2:;City:WIEN;Country:Austria;Postal Code:1010;State:;Instance Id :43286296; Host Name:zone03;Serial Number :HN35018754;Address Line 1:Air Surveillance Product Line (COESD5);Address Line 2:Wörthstr. 85;City:Ulm;Country:Germany;Postal Code:89077;State:;Instance Id :47110132; Serial Number :0724MM2093;Address Line 1:Hansaweg 90;Address Line 2:;City:Greven;Country:Germany;Postal Code:48268;State:;Instance Id :47820018; Serial Number :BEL075141G;Address Line 1:AQUARIUS BUILDING,;Address Line 2:ADASTRAL PARK,MARTLESHAM HEATH;City:IPSWICH;Country:United Kingdom;Postal Code:IP5 3RE;State:;Instance Id :48294133; Serial Number :TE34840069;Address Line 1:Air Surveillance Product Line (COESD5);Address Line 2:Wörthstr. 85;City:Ulm;Country:Germany;Postal Code:89077;State:;Instance Id :47961681; Serial Number :BCF093104K;Address Line 1:Kruppstr. 121-124;Address Line 2:;City:Frankfurt;Country:Germany;Postal Code:60388;State:;Instance Id :47152980;

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
Comment
Question by:pg1533
2 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 500 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

Open in new window

PAC-Standard-v1.1-multiple-lines.xlsm
0
 

Author Comment

by:pg1533
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Online interactive Microsoft Training 4 41
Batch Numbering 6 29
Transposing this formula ... 5 11
GPO to Load Add-ons for MS Office? 2 7
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

789 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