software

Alright here is a stumper for me, hopefully you guys can help. What i have is
a list of lamptypes and how many they are per light fixture. Then i want to
sum all of them to a summary column. I have the columns with sumif's to add
only the particular lamptype chosen. What i am trying to do is use the
advancefilter in a macro.
What i want is when i get done with my huge list of lamptypes is have the
macro pick up all the unique types and display them in a list. The problem
that is occuring is that the first lamp always repeats itself for some
reason.

Here is the VB Macro script:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/14/2005 by Andrew Slater
'
' Keyboard Shortcut: Ctrl Shift L
'
Range(quot;LAMPTYPEquot;).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
quot;AB5quot;), Unique:=True
End Sub

I can also send you a copy of the spreadsheet if you can really help me.
Just attach an email with your post.

AndyAdd a header to your list. Advanced filter is picking up the first entry in
your data as the header--not as a real value.

Andy wrote:
gt;
gt; Alright here is a stumper for me, hopefully you guys can help. What i have is
gt; a list of lamptypes and how many they are per light fixture. Then i want to
gt; sum all of them to a summary column. I have the columns with sumif's to add
gt; only the particular lamptype chosen. What i am trying to do is use the
gt; advancefilter in a macro.
gt; What i want is when i get done with my huge list of lamptypes is have the
gt; macro pick up all the unique types and display them in a list. The problem
gt; that is occuring is that the first lamp always repeats itself for some
gt; reason.
gt;
gt; Here is the VB Macro script:
gt;
gt; Sub Macro1()
gt; '
gt; ' Macro1 Macro
gt; ' Macro recorded 12/14/2005 by Andrew Slater
gt; '
gt; ' Keyboard Shortcut: Ctrl Shift L
gt; '
gt; Range(quot;LAMPTYPEquot;).AdvancedFilter Action:=xlFilterCopy,
gt; CopyToRange:=Range( _
gt; quot;AB5quot;), Unique:=True
gt; End Sub
gt;
gt; I can also send you a copy of the spreadsheet if you can really help me.
gt; Just attach an email with your post.
gt;
gt; Andy

--

Dave Peterson

Is it possible that there is a spelling discrepancy or a trailing or leading
space for one of the two values?
After you extract them, try something like =AB5=AB6 (or whatever cells the
two repeated values are in). If it comes back false, Excel is not
recognizing them as the same thing.

For what's it's worth, your macro worked fine on the dummy data I created.

--
tjquot;Andyquot; wrote:

gt; Alright here is a stumper for me, hopefully you guys can help. What i have is
gt; a list of lamptypes and how many they are per light fixture. Then i want to
gt; sum all of them to a summary column. I have the columns with sumif's to add
gt; only the particular lamptype chosen. What i am trying to do is use the
gt; advancefilter in a macro.
gt; What i want is when i get done with my huge list of lamptypes is have the
gt; macro pick up all the unique types and display them in a list. The problem
gt; that is occuring is that the first lamp always repeats itself for some
gt; reason.
gt;
gt; Here is the VB Macro script:
gt;
gt; Sub Macro1()
gt; '
gt; ' Macro1 Macro
gt; ' Macro recorded 12/14/2005 by Andrew Slater
gt; '
gt; ' Keyboard Shortcut: Ctrl Shift L
gt; '
gt; Range(quot;LAMPTYPEquot;).AdvancedFilter Action:=xlFilterCopy,
gt; CopyToRange:=Range( _
gt; quot;AB5quot;), Unique:=True
gt; End Sub
gt;
gt; I can also send you a copy of the spreadsheet if you can really help me.
gt; Just attach an email with your post.
gt;
gt; Andy
gt;

Try deleting the entire extract range and then run the macro again. If that
doesn't work, please describe the layout and the steps more thoroughly.

--
tjquot;Andyquot; wrote:

gt; Thanks guys, it makes sense but now i am getting a error that says quot;The
gt; extract range has a missing or illegal field namequot;
gt;
gt; quot;tjtjjtjtquot; wrote:
gt;
gt; gt; One more thought. If your Named Range does not include a Column Heading,
gt; gt; Excel is reading the top value in the list as a column heading. So it's not
gt; gt; exactly repeating - it's showing up as the Column Header and as an item in
gt; gt; the column.
gt; gt;
gt; gt; --
gt; gt; tj
gt; gt;
gt; gt;
gt; gt; quot;Andyquot; wrote:
gt; gt;
gt; gt; gt; Alright here is a stumper for me, hopefully you guys can help. What i have is
gt; gt; gt; a list of lamptypes and how many they are per light fixture. Then i want to
gt; gt; gt; sum all of them to a summary column. I have the columns with sumif's to add
gt; gt; gt; only the particular lamptype chosen. What i am trying to do is use the
gt; gt; gt; advancefilter in a macro.
gt; gt; gt; What i want is when i get done with my huge list of lamptypes is have the
gt; gt; gt; macro pick up all the unique types and display them in a list. The problem
gt; gt; gt; that is occuring is that the first lamp always repeats itself for some
gt; gt; gt; reason.
gt; gt; gt;
gt; gt; gt; Here is the VB Macro script:
gt; gt; gt;
gt; gt; gt; Sub Macro1()
gt; gt; gt; '
gt; gt; gt; ' Macro1 Macro
gt; gt; gt; ' Macro recorded 12/14/2005 by Andrew Slater
gt; gt; gt; '
gt; gt; gt; ' Keyboard Shortcut: Ctrl Shift L
gt; gt; gt; '
gt; gt; gt; Range(quot;LAMPTYPEquot;).AdvancedFilter Action:=xlFilterCopy,
gt; gt; gt; CopyToRange:=Range( _
gt; gt; gt; quot;AB5quot;), Unique:=True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; I can also send you a copy of the spreadsheet if you can really help me.
gt; gt; gt; Just attach an email with your post.
gt; gt; gt;
gt; gt; gt; Andy
gt; gt; gt;

Thanks guys, it makes sense but now i am getting a error that says quot;The
extract range has a missing or illegal field namequot;

quot;tjtjjtjtquot; wrote:

gt; One more thought. If your Named Range does not include a Column Heading,
gt; Excel is reading the top value in the list as a column heading. So it's not
gt; exactly repeating - it's showing up as the Column Header and as an item in
gt; the column.
gt;
gt; --
gt; tj
gt;
gt;
gt; quot;Andyquot; wrote:
gt;
gt; gt; Alright here is a stumper for me, hopefully you guys can help. What i have is
gt; gt; a list of lamptypes and how many they are per light fixture. Then i want to
gt; gt; sum all of them to a summary column. I have the columns with sumif's to add
gt; gt; only the particular lamptype chosen. What i am trying to do is use the
gt; gt; advancefilter in a macro.
gt; gt; What i want is when i get done with my huge list of lamptypes is have the
gt; gt; macro pick up all the unique types and display them in a list. The problem
gt; gt; that is occuring is that the first lamp always repeats itself for some
gt; gt; reason.
gt; gt;
gt; gt; Here is the VB Macro script:
gt; gt;
gt; gt; Sub Macro1()
gt; gt; '
gt; gt; ' Macro1 Macro
gt; gt; ' Macro recorded 12/14/2005 by Andrew Slater
gt; gt; '
gt; gt; ' Keyboard Shortcut: Ctrl Shift L
gt; gt; '
gt; gt; Range(quot;LAMPTYPEquot;).AdvancedFilter Action:=xlFilterCopy,
gt; gt; CopyToRange:=Range( _
gt; gt; quot;AB5quot;), Unique:=True
gt; gt; End Sub
gt; gt;
gt; gt; I can also send you a copy of the spreadsheet if you can really help me.
gt; gt; Just attach an email with your post.
gt; gt;
gt; gt; Andy
gt; gt;

One more thought. If your Named Range does not include a Column Heading,
Excel is reading the top value in the list as a column heading. So it's not
exactly repeating - it's showing up as the Column Header and as an item in
the column.

--
tjquot;Andyquot; wrote:

gt; Alright here is a stumper for me, hopefully you guys can help. What i have is
gt; a list of lamptypes and how many they are per light fixture. Then i want to
gt; sum all of them to a summary column. I have the columns with sumif's to add
gt; only the particular lamptype chosen. What i am trying to do is use the
gt; advancefilter in a macro.
gt; What i want is when i get done with my huge list of lamptypes is have the
gt; macro pick up all the unique types and display them in a list. The problem
gt; that is occuring is that the first lamp always repeats itself for some
gt; reason.
gt;
gt; Here is the VB Macro script:
gt;
gt; Sub Macro1()
gt; '
gt; ' Macro1 Macro
gt; ' Macro recorded 12/14/2005 by Andrew Slater
gt; '
gt; ' Keyboard Shortcut: Ctrl Shift L
gt; '
gt; Range(quot;LAMPTYPEquot;).AdvancedFilter Action:=xlFilterCopy,
gt; CopyToRange:=Range( _
gt; quot;AB5quot;), Unique:=True
gt; End Sub
gt;
gt; I can also send you a copy of the spreadsheet if you can really help me.
gt; Just attach an email with your post.
gt;
gt; Andy
gt;

Sorry for getting back so late, but i will try to describe the layout better.

Column 'R' is my Lamptype column; Cell R1 is blank, Cell R2 is the heading
quot;LAMP TYPEquot;,cells R3 amp; R4 are blank. Cells R5 and down are all my lamps. I
quot;LAMPTYPEquot;(Used in the formula/macro) defined in cells R5 and down to
automatically offset one cell down so i dont have to update list all the time.

Now i would like to use the advanced autofilter to move all the unique
lamptypes, w/o repeating them, into column T starting on cell T5. So the list
of lamptype should start on T5 and go down.

When it sorta works the first couple cells are the same, and the result is
that extracting point repeats the first row. If the first couple cells are
different then i get a error message quot;The extract range has a missing or
illegal field namequot;

I am going to give my old school email out in hope you can email me back so
i can then email you back the spreadsheet i am creating.

My old school email is

quot;tjtjjtjtquot; wrote:

gt; Try deleting the entire extract range and then run the macro again. If that
gt; doesn't work, please describe the layout and the steps more thoroughly.
gt;
gt; --
gt; tj
gt;
gt;
gt; quot;Andyquot; wrote:
gt;
gt; gt; Thanks guys, it makes sense but now i am getting a error that says quot;The
gt; gt; extract range has a missing or illegal field namequot;
gt; gt;
gt; gt; quot;tjtjjtjtquot; wrote:
gt; gt;
gt; gt; gt; One more thought. If your Named Range does not include a Column Heading,
gt; gt; gt; Excel is reading the top value in the list as a column heading. So it's not
gt; gt; gt; exactly repeating - it's showing up as the Column Header and as an item in
gt; gt; gt; the column.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; tj
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Andyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Alright here is a stumper for me, hopefully you guys can help. What i have is
gt; gt; gt; gt; a list of lamptypes and how many they are per light fixture. Then i want to
gt; gt; gt; gt; sum all of them to a summary column. I have the columns with sumif's to add
gt; gt; gt; gt; only the particular lamptype chosen. What i am trying to do is use the
gt; gt; gt; gt; advancefilter in a macro.
gt; gt; gt; gt; What i want is when i get done with my huge list of lamptypes is have the
gt; gt; gt; gt; macro pick up all the unique types and display them in a list. The problem
gt; gt; gt; gt; that is occuring is that the first lamp always repeats itself for some
gt; gt; gt; gt; reason.
gt; gt; gt; gt;
gt; gt; gt; gt; Here is the VB Macro script:
gt; gt; gt; gt;
gt; gt; gt; gt; Sub Macro1()
gt; gt; gt; gt; '
gt; gt; gt; gt; ' Macro1 Macro
gt; gt; gt; gt; ' Macro recorded 12/14/2005 by Andrew Slater
gt; gt; gt; gt; '
gt; gt; gt; gt; ' Keyboard Shortcut: Ctrl Shift L
gt; gt; gt; gt; '
gt; gt; gt; gt; Range(quot;LAMPTYPEquot;).AdvancedFilter Action:=xlFilterCopy,
gt; gt; gt; gt; CopyToRange:=Range( _
gt; gt; gt; gt; quot;AB5quot;), Unique:=True
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; I can also send you a copy of the spreadsheet if you can really help me.
gt; gt; gt; gt; Just attach an email with your post.
gt; gt; gt; gt;
gt; gt; gt; gt; Andy
gt; gt; gt; gt;