Counting and Organizing Data

[RalphSE]

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!

 

[Pete_UK]

Hi Ralph,

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

Pete

 

[Pete_UK]

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$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

 

[RalphSE]

Pete!!! You are my hero

 

[RalphSE]

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!

 

[Pete_UK]

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

 

[RalphSE]

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 matche
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 o
the excel master, LOL, got one brewing up..