+ Reply to Thread
Results 1 to 19 of 19

Importing values from sheets to create unique list

  1. #1
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Importing values from sheets to create unique list

    I'm trying to find the right wording to get my explanation out. Please bear with me (google didn't).

    I need to be able to pull information from 5 columns on 5 different pages, and pull all of the unique names out of it. From there, I need to create a list of all of these names. More often than not, this could only be three names, but I still would like this to be automatic because eventually it could be 50 or 60, and manually editing this list would get tedious. Lastly, is it possible to have a list create cells? For example: I have information on row 4, this one will start on row 6. My next group of information starts on row 14. That leaves 7 rows for information. I don't want to leave a large gap at the top of the page, so I'd like to be able to create cells if the list I'm trying to create has more information on it. Seems plausible in my mind, but I'm still new to excel.
    Last edited by hawkteflon; 06-08-2009 at 02:47 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    Mock up some sample data sheets and your "results" sheet. Showing us is much easier to envision then telling us...plus if your sample is accurate enough, the answer will be "ready to wear".

    Click GO ADVANCED and use the paperclip icon to upload your sample sheet(s).
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    here's where I am right now...
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    bueller? bueller?

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    I'm confused. Your sheet shows an apparently working solution. You have not pointed out where problems exist on your current sheet nor your preferred/different desired results.

    Can you clarify/amend your question/workbook?

  6. #6
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    Sorry, what I'm wanting to do is have a shorter summary on the summary page, above row 14. This section will tell how many flags have been distributed to each area in the US. However, I'm wondering if cells can be created when there is more information, and deleted when there is less automatically.

    For example, let's say I have 3 areas where flags have been sent out. I only want 3 rows to appear, one for each area where it will list the name. This should be a non-duplicating list UNLESS the contact information is different. But if there are 14 areas the flags are sent to, I want 14 rows to be present on the summary area that list these. Once they are returned, and the "location" has been deleted from other pages, I want it to automatically delete that row from "Summary" ... does this make sense?

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    SOLUTION 1:
    Right-click on the Summary tab and select VIEW CODE, then paste in this sheet-level macro:
    Please Login or Register  to view this content.
    It will re-evaluate the current sheet.
    =============
    SOLUTION 2:
    Another simpler non-macro way is to change the way the numbers are appearing on the summary. Change cell Summary!C3 to:

    =MAX('USA & Canada'!A:A)

    Then put this formula in A15 and copy down:
    =IF(ROW()-14>$C$3,"",A14+1)

    Delete that row 140 as unneeded. Now your summary will only show rows needed.

    ========
    Both of these approaches seem equally usable.
    ========
    FINAL NOTE:
    I'm a huge fan of the key column technique you're using in column A on all the country sheets, but the MAX() formula keeps reevaluating the entire column unnecessarily.

    On A5 in each of those sheets and copied down:

    =IF(C5="Yes",A4+1,A4)

    ...will do the same thing and greatly reduce the number of calculations going on.

  8. #8
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    Thanks for the Solution 2 in there! That's great!

    However, that's not really what I'm looking for. I'm looking for a NEW group of information above all of that. It will return all unique locations, and tell how many flags are there. For example, if I have 30 flags going to Georgia, 17 in NC, 12 in CA and so on, I need a tally of just how many are there. Not broken down like the list on 'Summary' but just a quick glance that will show me. Also, I need it to be able to CREATE cells if more information is added. Kinda like:

    Flag Locations:

    Georgia: 30
    NC: 17
    FL: 6

    skip a line, and then the next table starts. BUT, if I have 8 locations, I want it to be able to automatically update and add a row below the last. However, once those are off the list, it removes that unnecessary row. Is this possible?

    You may have done this in Solution 1, but I was lost. I'm an excel n00b.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    Are you describing a desire NOT shown in your sample sheet? If so, add a mockup of what you're saying and repost.

  10. #10
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    it is, kinda ... see the "flag locations" (b6) on summary? it would go between that and b13. I just made space for it. I don't know how to do it, so i can't put anything in there

  11. #11
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    Yes, you know what you want to appear there, you just don't know how to automate it. Fill it out manually so the end result is evident and repost.

  12. #12
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    ok, here's an updated version

    does this make sense?
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    anything? I'm still stumped

  14. #14
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    OK, thanks for bumping the thread again, since the recent forum upgrade I only get about 80% of the notifications that my threads have new posts in them...sorry about that.

    In C7 and copied down:

    =COUNTIF(F:F,LEFT(B7,FIND(":",B7)-1))

    If it weren't for the colon and the extra spaces you added in column B, it could have been simply:

    =COUNTIF(F:F,B7)

  15. #15
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    That works if there's something already in B7 and down. I'm wanting b7 to be filled in automatically by pulling information that's not duplicated from the chart below.

    THEN if there are more, I want it to add a row, if possible. Repeat as necessary.
    Like this:

    b7 pulls info from column f(15 and below), then checks to see if there is another unique place in there. if so, it adds a row with the same formulae prebuilt into the next row. If there is another unique bit of info, it repeats until all unique names are used. This probably isn't possible, is it?

  16. #16
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    Right-click on the Summary tab and select VIEW CODE. Paste in this macro:
    Please Login or Register  to view this content.
    Rather than deleting rows, we'll leave them there with their formulas doing the counting, each time you activate the sheet (come to it from another sheet) it will unhide the rows with values greater than zero, hide the zero rows.

    On this sample sheet, I also turned off the zero values for this one sheet so it looks cleaner.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    edit : somehow missed page 2

  18. #18
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing values from sheets to create unique list

    Is there more to do here or are we resolved? If that takes care of your need, be sure to EDIT your original post, click Go Advanced and add [SOLVED] to the title.


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

  19. #19
    Registered User
    Join Date
    05-29-2009
    Location
    Mustang, OK
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Importing values from sheets to create unique list

    his is great! you're a genius!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1