You are here:

Excel/Macro to Auto Sum & Auto Print Unique Values


QUESTION: Hello!  Below is an example of a spreadsheet I will be creating for a charity event that I need to auto-print "invoices" for after the event is over.  

Item #   Item   Bidder # $ Bid
1   Hat   456   $450.00
2   Shorts   345   $200.00
3   Bike   333   $4.00
4   Cap   222   $66.00
5   TV   456   $323.00
6   Radio   333   $55.00
7   Couch   555   $150.00
8   Toaster   333   $75.00

Essentially, I need a macro that will look through the Bidder # column, pull out each unique value, sum up the values in the $ Bid column for each unique Bidder #, Auto-Print the Bidder information (both a total for all items won, and a list of each of the items they've won) using an invoice-esque layout (that I have yet to create), and then repeat the above steps based on the next unique Bidder #.

The macro would need to continue through the Bidder # list until it had printed all of the unique Bidder # values.

I've tried to piece-meal several macros I've found online but haven't found a combination that works.   So, I'm turning to the experts!
Many thanks in advance for any help!

ANSWER: This is not a trivial query.

On my website (link below) are many PARSE scripts that may speed up your design process.  Sheet1 to Sheets could easily split your consolidated data into separate sheets, at least making quick work of that part.

There's other macros with other concepts.  If you had a template already setup to put date into, you might be able to adapt the "Data into Template" macro to take data from each of the created sheets instead of from a row-based database.

---------- FOLLOW-UP ----------

QUESTION: Thanks for replying, Jerry!  After testing Parse macros you mentioned, one of them, Sheet1toSheets-ParseMacros.xls, looks like it would fit the bill almost perfectly.  The only modification I would like to make to the code is have the data copied NOT start in Row 1 but in a row further down.  I couldn't quite see how/where they copy was placing the data into the new sheets.  Is there a way to have the pasted data start in a different row than row 1?

ANSWER: The line of code with the word COPY in it is the clue.   The second half indicates the target:

       ws.Range("A" & TitleRow & ":A" & LR).EntireRow.Copy _
         Sheets(MyArr(Itm) & "").Range("A1")

Change the Range("A1") to the target cell you want.

---------- FOLLOW-UP ----------

QUESTION: Wow.  I'm not sure if you're an ass, arrogant, trying to be condescending, or just a plain old-fashioned idiot.  I asked a simple question and got a snide response.  "The line of code with the word COPY in it is the clue."  Whether you meant it to be rude or not, I suggest you invest in some remedial communication 101 classes and learn not be a dink when someone seeking out your EXPERTISE is responded to with insolence.  If you're not sure why I feel this way, "The line of code with the word COPY in it is the clue." is YOUR clue...

I'll take the high road though and still thank you for your assistance, impertinent as it turned out to be...

My friend,  Brevity can read harshly, can't it?  I'll be more verbal in this message.

It's is a known truth that many times the answer to one's own questions are revealed when a well-worded question is written out.  This happens to me weekly as I present a quandary to a colleague via email or a forum, the answer to my question jumps out as I write the question out fully.  There's actually a term "rubber duck programming" that relates to a similar truth about talking to someone about something reveals the answer, even if talking to yourself (or a rubber duck on your desk).  Heh.

Anyway, I'm sorry you read a tone into my response that certainly was not there, or better stated was not intended.    I answer 100s of questions per week on multiple sites, so move from question to question rather quickly.  There was no condescending tone intended nor written into that short reply.  

I'm a teacher by instinct.  So I frequently point to the self-teaching clues within a person's own question.  Suggestion - if you read a message and picture someone talking down to you, read it again and picture a teacher talking about your question with you.  If with that image in mind the message still sounds offensive, then perhaps there is something there.  In this case I'd hope you'd have discovered that there isn't.

I'll continue to invest my time in helping others as that's the best I have to offer.  Sorry again for your misread of my assistance.  See you next time.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques:


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: ===================== I have been offering free assistance as an Excel aid on many web sites for many years: ( - JBeaucaire) ======== ( - JBeaucaire) ======= ( - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 All rights reserved.