+ Reply to Thread
Results 1 to 7 of 7

Counting and Organizing Data

  1. #1
    Registered User
    Join Date
    12-27-2005
    Posts
    72

    Counting and Organizing Data

    Hi,

    I am in need of some excel kung fu here, just a few slick formulas to accomplish a data organizing task. I have described the task in detail here:

    http://www.flypicture.com?display=updone&id=rtv2kK7c

    THANKS!

  2. #2
    Pete_UK
    Guest

    Re: Counting and Organizing Data

    Hi Ralph,

    just give me a few minutes to download and examine your file ...

    Pete


  3. #3
    Pete_UK
    Guest

    Re: Counting and Organizing Data

    Ok, fairly straightforward this one - in C3 of your NHNL sheet, enter
    the formula:

    =COUNTIF(START!B$3:B$213,B3)

    and in D3:

    =COUNTIF(START!D$3:D$213,B3)

    You will have to copy these down in small blocks so as to retain your
    subtotals for the industries - tedious, but not difficult. You will
    notice that I've used the longest range in both formulae - if you have
    longer lists the next time you do this, just highlight columns C and D
    and use Find & Replace (CTRL-H) to change $213 to $whatever (or you
    could change them to, say, $500, if you think that is enough to cover
    all future lists).

    You will also have to change your %age formula in column E to something
    like (in E2):

    =IF(C2+D2)=0,0,C2/(C2+D2))

    This will avoid the #DIV/0! error if both C and D are zero. This can be
    copied all the way down, then just re-instate your shading as
    necessary.

    Hope this helps. Ah! So!

    Pete


  4. #4
    Registered User
    Join Date
    12-27-2005
    Posts
    72
    Pete!!! You are my hero

  5. #5
    Registered User
    Join Date
    12-27-2005
    Posts
    72
    Pete, got an easy one for you, should be able to answer this blindfolded, LOL


    if i have 2 lists of those subindustries, but they are different lists, how can i get a 3rd list of only those subindustries that are in both lists??

    lets say list one is in column A, list 2 in column B, want 3rd resulsts list in column C

    talk to me Pete!

  6. #6
    Pete_UK
    Guest

    Re: Counting and Organizing Data

    Hi Ralph,

    maybe not quite what you want, but it's nearly bed-time here in the UK
    (1:00 am).

    I've used your sample file from earlier, and in F3 of the START sheet
    I've entered this formula:

    =IF(ISNA(MATCH(D3,B$3:B$213,0)),"",D3)

    Basically, if there isn't a match between the element of the smaller
    array that we are looking at (i.e. D3) and the larger array, then
    return blank, otherwise return D3. Copy this down to the bottom of the
    smaller array (in this case F26).

    This gives you the items which are common in both lists, but also gives
    you blanks between. You could fix the values with paste special, then
    sort the items in this list so that the blanks drop to the bottom.

    Hope this helps - I'll read any reply in the morning.

    Good night.

    Pete


  7. #7
    Registered User
    Join Date
    12-27-2005
    Posts
    72
    works fantastic Pete thanks my friend!!

    minor question if I wanted to modify your formula:

    =IF(ISNA(MATCH(D3,B$3:B$213,0)),"",D3)

    such that the 2 data sets are in columns A & B and I want the matches in C, would the modification be:

    =IF(ISNA(MATCH(B2,A$2:A$213,0)),"",B2) ???


    P.S. just wait till my later post, this one will be the true test of the excel master, LOL, got one brewing up...

+ 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