Creation of desired data (Output) from EDI file (Input)

**RavindraK** · 10-10-2018, 06:30 AM

Dear Excel Experts,

As a new practice, we have prepare data (OUTPUT) from avaibale (INPUT)

Into INPUT worksheet 1st 9 rows are header part
from 10th row container data is there

Requirement / defination..
POSITION After text LOC+147+0, 3 characters
For Example : 131214 or 111116 etc.

WT require text in between +WT++KGM: and '
For Example : 16400 or 24700 etc

WT (VGM) require text in between +VGM++KGM: and '
For Example : 16400 or 24700 etc

POD After text LOC+9+ , 5 characters
For Example : CNTAO or CNSHA etc.

POL After text LOC+11+ , 5 characters
For Example : INMUN or INHZA etc.

Contaienr NO After text EQD+CN+ , 11 characters
For Example : AXIU2018854 or BMOU2709770 etc.

Size After text EQD+CN+11characters+ , 4 characters
For Example : 2200 or 4500 etc.

Rfr If contain TMP+2+ text, require text in between TMP+2+ and ..: & a character after :
For Example : -18C or +21 etc.

Haz If contain DGS+IMD+ text, require text in between DGS+IMD+ and ..+
For Example : 4.1 or 9 etc.

UN If contain DGS+IMD+ text, require 4 character after text DGS+IMD+ ..+
For Example : 2670 or 2211 etc.
File attached for detailes (i have done manually for few container's details not all)
Thanks in a davance
RavindraK

**RavindraK** · 10-14-2018, 10:44 PM

Dear Excel Experts,
Please help me to resolve the mentioned query.
Thanks in a advance.

Ravindrak

**JeteMc** · 10-14-2018, 11:45 PM

This proposed solutions employs 12 helper columns (B:M) with various formulas to extract data from column A.
Note that the helper columns may be moved and/or hidden for aesthetic purposes.
The output range is in columns O:Z with column O containing the the container ID's (1...2345)
Columns P:Z are populated using:

Formula:

=IFERROR(INDEX(C$10:C$20943,AGGREGATE(15,6,(ROW($10:$20943)-9)/($B$10:$B$20943=$O9)/(C$10:C$20943<>""),1)),"")

Note that I reduced the number of containers to 100 in order to meet the size constraints for uploading, however you should be able to complete the output table if you:
1) paste the original data into column A starting with A913
2) select B912:M912 and double click the fill handle
3) select P108:Z108 and double click the fill handle (this process may take some time)
Let us know if you have any questions.

**RavindraK** · 10-15-2018, 01:05 AM

Superb Sir,
You have done, what i have expected, only minor bug is...
It is taking some time for opening and execution as it contains formula maximum based rows i.e. 6000 (i have extended from 2345 to 6000) .
Can it be modified for fast execution?
Ravindrak

**FlameRetired** · 10-15-2018, 10:38 AM

I can't find a way to do fast execution. One minor difference in JeteMc's upload is to replace the IFERROR. This is about 1½ - 2 seconds faster in P9:Z108. Since the numbers are actually text "numbers" this covers the errors

Formula:

=LOOKUP("zzzz",CHOOSE({1,2},"",INDEX(C$10:C$20943,AGGREGATE(15,6,(ROW($10:$20943)-9)/($B$10:$B$20943=$O9)/(C$10:C$20943<>""),1))))

is nothing to write home about. It may have more impact on large data though. Try it and see.

**RavindraK** · 10-16-2018, 01:33 AM

Can it be done through macro, becasue macro will more faster than formulae

This is just request Sirs. please check if possible.

Thanks in a advance.

Ravindrak

**RavindraK** · 11-05-2018, 08:18 AM

Dear Excel Experts,

Can it be done through macro, becasue after deleteing old text and adding new text everytime, it hangs the system.

Thanks in a advance.

Ravindrak

**RavindraK** · 11-07-2018, 01:37 AM

Dear Excel Experts,

Please help me to resolve the problem.

Thanks in a advance.

Ravindrak

**RavindraK** · 11-11-2018, 10:48 PM

Dear Excel Experts,

Kindly help me to resolve the problem.

Thanks in advance.

Ravindrak

**Winon** · 11-14-2018, 03:02 AM

Hello RavindraK,

Can it be done through macro, because macro will more faster than formulae

VBA and Macros are actually slower that any Standard Built in Excel Functions.

You are covering a very large Range to be Calculated, hence the "delay" in processing your formulae.

Regards.

**RavindraK** · 11-23-2018, 06:01 AM

Can anyone please help me to resolve my query.
Thanks in advance.
Ravindrak

**bakerman2** · 11-25-2018, 12:54 PM

Try this one.

Sub tst()
    t = Timer
    Dim res
    With Blad1
        sn = .Range("A10", .Range("A" & Rows.Count).End(xlUp))
    End With
    ReDim res(1 To UBound(sn) / 5, 1 To 11)
    rowNum = 0
    For i = 1 To UBound(sn)
        If Left(sn(i, 1), 7) Like "LOC+147" Then
            rowNum = rowNum + 1
            res(rowNum, 1) = Mid(sn(i, 1), 9, InStr(1, sn(i, 1), ":") - 9)
            
        ElseIf Left(sn(i, 1), 6) Like "MEA+WT" Then
            res(rowNum, 2) = Mid(sn(i, 1), 13, InStr(1, sn(i, 1), "'") - 13)
            
        ElseIf Left(sn(i, 1), 7) Like "MEA+VGM" Then
            res(rowNum, 3) = Mid(sn(i, 1), 14, InStr(1, sn(i, 1), "'") - 14)
            
        ElseIf Left(sn(i, 1), 5) Like "LOC+9" Then
            res(rowNum, 4) = Mid(sn(i, 1), 7, InStr(1, sn(i, 1), ":") - 7)
            
        ElseIf Left(sn(i, 1), 6) Like "LOC+11" Then
            res(rowNum, 5) = Mid(sn(i, 1), 8, InStr(1, sn(i, 1), ":") - 8)
        
        ElseIf Left(sn(i, 1), 6) Like "EQD+CN" Then
            res(rowNum, 6) = Mid(sn(i, 1), 8, 11)
            res(rowNum, 7) = Mid(sn(i, 1), 20, 4)
        
         ElseIf Left(sn(i, 1), 4) Like "TMP+" Then
            res(rowNum, 8) = Mid(sn(i, 1), 7, InStr(1, sn(i, 1), ":") - 7)
            
        On Error Resume Next
        ElseIf Left(sn(i, 1), 7) Like "DGS+IMD" Then
            res(rowNum, 9) = Mid(sn(i, 1), 9, InStr(9, sn(i, 1), "+") - 9)
            res(rowNum, 10) = Split(Replace(sn(i, 1), "+", "/", 9, 2), "/")(1)
        On Error GoTo 0
        
        ElseIf Left(sn(i, 1), 6) Like "NAD+CA" Then
            res(rowNum, 11) = Mid(sn(i, 1), 8, InStr(1, sn(i, 1), ":") - 8)
        End If
    Next
    With Blad1.Cells(1, 5)
        .CurrentRegion.Offset(1).ClearContents
        .Offset(1).Resize(UBound(res), 11) = res
    End With
    MsgBox Timer - t
End Sub

**RavindraK** · 12-10-2018, 06:36 AM

Dear Sir,
I am unable to open attached file.
Also tried to add your given code in excel file but showing error at belwo line
"sn = .Range("A10", .Range("A" & Rows.Count).End(xlUp))"

Thanks in advance.
Ravindrak

**bakerman2** · 12-10-2018, 10:57 AM

Try this one.

**RavindraK** · 12-10-2018, 11:08 PM

Dear Sir,

Executing as expected, excluding small error....

Some of DGUN are appearing with single quotations mark (‘)
Appearing Desired
3349' 3349
3082' 3082
3272' 3272
3352' 3352

Please resolve the same.
Thanks in advance.
Ravindrak

**bakerman2** · 12-11-2018, 02:26 AM

Sub tst()
    Dim res
    With Blad1
        sn = .Range("A10", .Range("A" & Rows.Count).End(xlUp))
    End With
    ReDim res(1 To UBound(sn) / 5, 1 To 11)
    rowNum = 0
    For i = 1 To UBound(sn)
        If Left(sn(i, 1), 7) Like "LOC+147" Then
            rowNum = rowNum + 1
            res(rowNum, 1) = Mid(sn(i, 1), 9, InStr(1, sn(i, 1), ":") - 9)
            
        ElseIf Left(sn(i, 1), 6) Like "MEA+WT" Then
            res(rowNum, 2) = Mid(sn(i, 1), 13, InStr(1, sn(i, 1), "'") - 13)
            
        ElseIf Left(sn(i, 1), 7) Like "MEA+VGM" Then
            res(rowNum, 3) = Mid(sn(i, 1), 14, InStr(1, sn(i, 1), "'") - 14)
            
        ElseIf Left(sn(i, 1), 5) Like "LOC+9" Then
            res(rowNum, 4) = Mid(sn(i, 1), 7, InStr(1, sn(i, 1), ":") - 7)
            
        ElseIf Left(sn(i, 1), 6) Like "LOC+11" Then
            res(rowNum, 5) = Mid(sn(i, 1), 8, InStr(1, sn(i, 1), ":") - 8)
        
        ElseIf Left(sn(i, 1), 6) Like "EQD+CN" Then
            res(rowNum, 6) = Mid(sn(i, 1), 8, 11)
            res(rowNum, 7) = Mid(sn(i, 1), 20, 4)
        
         ElseIf Left(sn(i, 1), 4) Like "TMP+" Then
            res(rowNum, 8) = Mid(sn(i, 1), 7, InStr(1, sn(i, 1), ":") - 7)
            
        On Error Resume Next
        ElseIf Left(sn(i, 1), 7) Like "DGS+IMD" Then
            res(rowNum, 9) = Mid(sn(i, 1), 9, InStr(9, sn(i, 1), "+") - 9)
            res(rowNum, 10) = Replace(Split(Replace(sn(i, 1), "+", "/", 9, 2), "/")(1), "'", "")
        On Error GoTo 0
        
        ElseIf Left(sn(i, 1), 6) Like "NAD+CA" Then
            res(rowNum, 11) = Mid(sn(i, 1), 8, InStr(1, sn(i, 1), ":") - 8)
        End If
    Next
    With Blad1.Cells(1, 5)
        .CurrentRegion.Offset(1).ClearContents
        .Offset(1).Resize(UBound(res), 11) = res
    End With
End Sub

**RavindraK** · 12-11-2018, 02:41 AM

Thanks a lot Sir,
It's running perftectly, you are really charm !!

I tried to changing name from Blad1 to Ravi; changed the same in code as well as worksheet/tab name, but after changing it is not running, can you do something for the same

Ravindrak

**bakerman2** · 12-11-2018, 10:15 AM

Sub tst()
    Dim res
    With Sheets("Ravi")
        sn = .Range("A10", .Range("A" & Rows.Count).End(xlUp))
    End With
    ReDim res(1 To UBound(sn) / 5, 1 To 11)
    rowNum = 0
    For i = 1 To UBound(sn)
        If Left(sn(i, 1), 7) Like "LOC+147" Then
            rowNum = rowNum + 1
            res(rowNum, 1) = Mid(sn(i, 1), 9, InStr(1, sn(i, 1), ":") - 9)
            
        ElseIf Left(sn(i, 1), 6) Like "MEA+WT" Then
            res(rowNum, 2) = Mid(sn(i, 1), 13, InStr(1, sn(i, 1), "'") - 13)
            
        ElseIf Left(sn(i, 1), 7) Like "MEA+VGM" Then
            res(rowNum, 3) = Mid(sn(i, 1), 14, InStr(1, sn(i, 1), "'") - 14)
            
        ElseIf Left(sn(i, 1), 5) Like "LOC+9" Then
            res(rowNum, 4) = Mid(sn(i, 1), 7, InStr(1, sn(i, 1), ":") - 7)
            
        ElseIf Left(sn(i, 1), 6) Like "LOC+11" Then
            res(rowNum, 5) = Mid(sn(i, 1), 8, InStr(1, sn(i, 1), ":") - 8)
        
        ElseIf Left(sn(i, 1), 6) Like "EQD+CN" Then
            res(rowNum, 6) = Mid(sn(i, 1), 8, 11)
            res(rowNum, 7) = Mid(sn(i, 1), 20, 4)
        
         ElseIf Left(sn(i, 1), 4) Like "TMP+" Then
            res(rowNum, 8) = Mid(sn(i, 1), 7, InStr(1, sn(i, 1), ":") - 7)
            
        On Error Resume Next
        ElseIf Left(sn(i, 1), 7) Like "DGS+IMD" Then
            res(rowNum, 9) = Mid(sn(i, 1), 9, InStr(9, sn(i, 1), "+") - 9)
            res(rowNum, 10) = Replace(Split(Replace(sn(i, 1), "+", "/", 9, 2), "/")(1), "'", "")
        On Error GoTo 0
        
        ElseIf Left(sn(i, 1), 6) Like "NAD+CA" Then
            res(rowNum, 11) = Mid(sn(i, 1), 8, InStr(1, sn(i, 1), ":") - 8)
        End If
    Next
    With Sheets("Ravi").Cells(1, 5)
        .CurrentRegion.Offset(1).ClearContents
        .Offset(1).Resize(UBound(res), 11) = res
    End With
End Sub

**RavindraK** · 12-11-2018, 10:37 PM

Yesterday, i marked this post as SOLVED but still you have resolved small issue.
Thanks a lot sir for your great help, hats off to you Boss.
Ravindrak

**Pepe Le Mokko** · 12-26-2018, 04:37 AM

It has come to our attention you have violated Rule 8 of our Forum RULES. Don't Private Message, Visitor message or email Excel questions to moderators or other members. (Or Access, Word, etc.)

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to permanent ban, so do take this caution to heart.

Creation of desired data (Output) from EDI file (Input)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Re: Creation of desired data (Output) from EDI file (Input)

Thread Information

Users Browsing this Thread

Similar Threads

Macro use input file name as output name

[SOLVED] Formula needed to help convert input data into desired output data format, thank you

[SOLVED] Web Query - Website requires data input before it shows desired data

Auto creation of sheets based on Data sheet with a sample output shown next to it

Indirect function to pull desired no of cells/row data based on data validation in Input

[SOLVED] Hyperlink to file. Input/Output processing error Reported

creation of data input window in excel

Bookmarks

Bookmarks

Posting Permissions