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!
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!
Hi Ralph,
just give me a few minutes to download and examine your file ...
Pete
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
Pete!!! You are my hero
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!
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
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...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks