Name as per type column

**sam1105** · 10-23-2015, 02:55 AM

Hi Guys,

Please find the attached sheet, the formula which i am using doesnt show the 2nd criteria, i have highlighted my result,
I remove duplicates from the shot column & then i run formula...
My problem is 118_SQ001_Sc001 has 2 SET & 2 PROP but my formula shows only 1 that is too first one

I need both answers , i.e. both the SET & PROP

Note - I need my solution from column A,B &C

Link of my previous post - http://www.excelforum.com/excel-form...ml#post4214768

**Glenn Kennedy** · 10-23-2015, 03:35 AM

I think I follow what you want...

I created a helper column

=A2&B2&C2

and then made a unique list from the helper column(array entered)

=IFERROR(INDEX($D$2:$D$596,MATCH(0,COUNTIF($J$1:J1,$D$2:$D$596),0)),"")

I then used that in an INDEX-MATCH to return your results, dragging across and down:
=INDEX(A$2:A$596,MATCH($J2,$D$2:$D$596,0))

**Glenn Kennedy** · 10-23-2015, 03:42 AM

My www connection died! Here's the attachment

**JohnTopley** · 10-23-2015, 04:11 AM

Is the attached example (No formula) the output expected?

**sam1105** · 10-23-2015, 05:00 AM

Hii glen,

please find attached sheet .. need solution like this.. i m srry for not explaining it properly

**Glenn Kennedy** · 10-23-2015, 05:05 AM

I would have helped if you had shown that in the first place. This is a TOTALLY different question. You will need VBA for this. Is that OK?

**sam1105** · 10-23-2015, 05:07 AM

Yes...Glennn....i m okay with it...and i m really sorry as my manager askd me this now...hope u understand...

**JohnTopley** · 10-23-2015, 07:48 AM

See attached

Sub Transform()
Dim vSet As String
Dim vChar As String
Dim vProp As String
Dim vShot As String

Dim r As Long
Dim rr As Long

rr = 2
r = 3

Do While Cells(r, 1) <> ""

    vShot = Cells(r, 1)
    
    vSet = ""
    vChar = ""
    vProp = ""
    
    Do While Cells(r, 1) = vShot
        If Cells(r, 3) = "SET" Then
            vSet = vSet & Cells(r, 2) & ","
            Else
                If Cells(r, 3) = "Character" Then
                vChar = vChar & Cells(r, 2) & ","
                Else
                vProp = vProp & Cells(r, 2) & ","
                End If
        End If
        r = r + 1
    Loop
    
    If vSet <> "" Then vSet = Left(vSet, Len(vSet) - 1)
    If vChar <> "" Then vChar = Left(vChar, Len(vChar) - 1)
    If vProp <> "" Then vProp = Left(vProp, Len(vProp) - 1)
    
    Cells(rr, 5) = vShot
    Cells(rr, 6) = vSet
    Cells(rr, 7) = vChar
    Cells(rr, 8) = vProp
    
    rr = rr + 1
    
Loop

End Sub

**sam1105** · 10-23-2015, 08:32 AM

Hi JohnTopley

Its works great only 1 thing it is giving me a error after a press the button "Run time Error 13" Type Mismatch..

Please find attached sheet

**JohnTopley** · 10-23-2015, 08:47 AM

Attached has "Error handling"

**JohnTopley** · 10-23-2015, 08:40 AM

Row 415 has a "#N/A" error code in it so the macro fails.

**sam1105** · 10-23-2015, 08:56 AM

Thank you ..it works great....

**sam1105** · 10-26-2015, 02:57 AM

Hi JohnTopley,

1problem is here 118_SQ001A_Sc001 shot is not in the list after running macro & 1 more thing is shot nos are repeating & i want duplicate to be removed in shot nos.i.e. ("E Column")

Please help..

**JohnTopley** · 10-26-2015, 04:02 AM

See attached: data sorted and cannot see duplicates (?)

**sam1105** · 10-26-2015, 05:23 AM

Hi JohnTopley,

If you see Column A has total 596 shots, and if i remove duplicates (Data- Remove Duplicates) from it i should get total 121 shots, but E column it is 133 shots...

Please see the highlighted part in the attachment

**JohnTopley** · 10-26-2015, 08:17 AM

All duplicates are NOT removed: and as the file isn't sorted I don't check for duplicates as it was not stated as a requirement.

See attached SORTED file.

**sam1105** · 10-26-2015, 08:50 AM

I think i m not clear ..but it still doesnt removed duplicates from Column E..pls check it again...i have manually removed duplicated and pasted in column M.. this is correct result which should appear in Column E... i Hope i m clear now...

**JohnTopley** · 10-26-2015, 09:04 AM

Sorry but there many duplicates in columns A to C and as I explained earlier: (1) the file needs to be sorted by A,B and C to find the duplicates (2) the macro does NOT check for duplicates,

Remove all the duplicates and it will give the correct result.

**JohnTopley** · 10-26-2015, 09:19 AM

Results with file sorted and check for duplicates.

   
Sub Transform()
Dim vSet As String
Dim vChar As String
Dim vProp As String
Dim vShot As String
Dim VDuplicate As String

Dim r As Long
Dim rr As Long

rr = 2
r = 2

On Error GoTo nextr

Do While Cells(r, 1) <> ""

    vShot = Cells(r, 1)
    
    vSet = ""
    vChar = ""
    vProp = ""
    VDuplicate = ""
    
    Do While Cells(r, 1) = vShot
        If VDuplicate = Cells(r, 1) & Cells(r, 2) & Cells(r, 3) Then GoTo nextr
        If Cells(r, 3) = "SET" Then
            vSet = vSet & Cells(r, 2) & ","
            Else
                If Cells(r, 3) = "Character" Then
                vChar = vChar & Cells(r, 2) & ","
                Else
                vProp = vProp & Cells(r, 2) & ","
                End If
        End If
nextr:
        VDuplicate = Cells(r, 1) & Cells(r, 2) & Cells(r, 3)
        r = r + 1
    Loop
    
    If vSet <> "" Then vSet = Left(vSet, Len(vSet) - 1)
    If vChar <> "" Then vChar = Left(vChar, Len(vChar) - 1)
    If vProp <> "" Then vProp = Left(vProp, Len(vProp) - 1)
    
    Cells(rr, 5) = vShot
    Cells(rr, 6) = vSet
    Cells(rr, 7) = vChar
    Cells(rr, 8) = vProp
    
    rr = rr + 1
    
Loop

End Sub

**sam1105** · 10-27-2015, 05:53 AM

HI John,

Sorry for delay response..the website was not opening because of some sucuri firewall backend error..please ignore the duplicates and can u pls also code in the attached sheet for "FX " part too as coding doesnt pull FX and rest everything is working grt...

find the attached document and pls do the changes for only FX part in the coding...
Thanks a lott....

**JohnTopley** · 10-27-2015, 11:35 AM

See attached.

**sam1105** · 10-28-2015, 03:32 AM

Hi

Thanks a lot for your hep & patience....my problem is solved now...and can u pls suggest some easy macro learning sites.. if you dont mind...Thank You again...

**JohnTopley** · 10-28-2015, 04:18 AM

Look here for recommendations: I have an earlier John Walkenbach book and can certainly recommend him.

HTML Code:

Hybrid View

Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Re: Name as per type column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] SUM All Column per Row Based on Type of Column

Bookmarks

Bookmarks

Posting Permissions