IS this possible? #2

Hi there. I have the following in Excel:


    A        B    C    D
1  hello    1
2  test     0
3  bye     1
4  hi        0
5  bybye  0
6  tjou     2


now on another place in this sheet i want to see all the words under each
other where the B value is bigger the 0 so:

    E    F    G

1  hello
2  bye
3  tjou


So i only want to show th'ose words that have a bigger value. and i don't
want whitelines between the words like this ( =IF(B1>0,A1,"") )


    E        F    G
1  hello
2
3  bye
4
5
6  tjou



0
jw9613 (4)
7/6/2005 8:13:18 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
810 Views

Similar Articles

[PageSpeed] 7

One non-array play ..

Use an empty col to the right, say col H

Put in H1: =IF(B1>0,ROW(),"")
Copy H1 down to say H100 to cover the max expected data in cols A and B

Put in E1:
=IF(ISERROR(SMALL(H:H,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(H:H,ROWS($A$1
:A1)),H:H,0)))

Copy E1 down to E100
(cover the same range as col H)

Col E will return the desired results from col A, all bunched neatly at the
top

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Janwillem" <jw@nietmailen.nl> wrote in message
news:42cb8f5d$0$2453$ba620dc5@text.nova.planet.nl...
> Hi there. I have the following in Excel:
>
>
>     A        B    C    D
> 1  hello    1
> 2  test     0
> 3  bye     1
> 4  hi        0
> 5  bybye  0
> 6  tjou     2
>
>
> now on another place in this sheet i want to see all the words under each
> other where the B value is bigger the 0 so:
>
>     E    F    G
>
> 1  hello
> 2  bye
> 3  tjou
>
>
> So i only want to show th'ose words that have a bigger value. and i don't
> want whitelines between the words like this ( =IF(B1>0,A1,"") )
>
>
>     E        F    G
> 1  hello
> 2
> 3  bye
> 4
> 5
> 6  tjou
>
>
>


0
demechanik (4694)
7/6/2005 8:22:24 AM
How about using Data>Filter>Advanced Filter with a criteria of <> 0 and
unique records only.

-- 
 HTH

Bob Phillips

"Janwillem" <jw@nietmailen.nl> wrote in message
news:42cb8f5d$0$2453$ba620dc5@text.nova.planet.nl...
> Hi there. I have the following in Excel:
>
>
>     A        B    C    D
> 1  hello    1
> 2  test     0
> 3  bye     1
> 4  hi        0
> 5  bybye  0
> 6  tjou     2
>
>
> now on another place in this sheet i want to see all the words under each
> other where the B value is bigger the 0 so:
>
>     E    F    G
>
> 1  hello
> 2  bye
> 3  tjou
>
>
> So i only want to show th'ose words that have a bigger value. and i don't
> want whitelines between the words like this ( =IF(B1>0,A1,"") )
>
>
>     E        F    G
> 1  hello
> 2
> 3  bye
> 4
> 5
> 6  tjou
>
>
>


0
phillips1 (803)
7/6/2005 8:34:54 AM
Reply:

Similar Artilces:

Outlook Rules: possible to use 'AND' operator?
In Outlook 2003, I can't find a way to use the 'and' operator when setting up a rule. For example, if I create a rule that moves messages with specific words in the subject, Outlook inserts the 'or' operator in between each word that I add. Is there a way to use 'and' instead? Thanks... As far as I know, no. "D.P. Roberts" <fakeaddress@fake.com> wrote in message news:es6$wwuuFHA.3124@TK2MSFTNGP12.phx.gbl... > In Outlook 2003, I can't find a way to use the 'and' operator when setting > up a rule. For example, if I create a rule...

=>Losing Money like MAD? Don't feel bad. Get RAD! #2
FREE, Meaty and Fun new eCourse: How to make ANY at-home online business pay. http://trackthishit.com/trackbiz/control/redirect.php?id=4125 ---------------------------------------------- Discover the World's #1 home business here: http://trackthishit.com/trackbiz/control/redirect.php?id=4126 --- MAF Anti-Spam ID: 20040421165609T2m7RlE6 ...

RMS Store OPS 1.3/2.0 to GP 10 General Ledger Integration
Can anyone tell me if this integration is available yet? I have a client running Store Ops 1.3 and GP 9.0 wanting to go to GP 10. (They don't have HQ). I couldn't find anything in Partnersource on this topic. Don Hi, If you are interested we can discuss about offering custom Integration soluition for RMS-GP. Please drop a email. Regards, manick.m@hotmail.com "doninep" wrote: > Can anyone tell me if this integration is available yet? I have a client > running Store Ops 1.3 and GP 9.0 wanting to go to GP 10. (They don't have > HQ). I couldn't f...

Outlook Calender #2
Since upgrading from Office 97 to Office XP the ability to edit existing calendar appointments has gone. Has anyone experienced this and come up with a solution? Please define "gone." Error messages, unexpected results, what??? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Nick <qubic808@nildram.co.uk> asked: | Since upgrading from Office 97 to Office XP the ability to | edit existing calendar appointments has gone. Has anyone | experienced this and come up with a ...

The format of the e-mail address is incorrect #2
I have a new Exchange 2003 SP2 server in the same admin group as an existing Exchange 2003 SP1 server. The new server can send email to anybody on the older server, but the old server can not send email to anybody on the new server. The error that the user on the old server gets back is: Joe Test on 3/7/2007 10:51 PM The format of the e-mail address is incorrect. Check the address, look up the recipient in the Address Book, or contact the recipient directly to find out the correct address. <exchvs.internal.domain #5.1.3> The user that is on the new server does appear in the GAL o...

Office 2008 won't install #2
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I place my program disk in the DVD player and it ejects without installing "Jacknc@officeformac.com" wrote: > I place my program disk in the DVD player and it ejects without installing Check the DVD Player setting re-set it to the max speed. Test in another DVD player to see if it's a bad disk. -- Diane ...

Credit Card setup #2
Hi, i just install the pc-charge, and i seted up the rms 2.0 to work with the pc-charge, when i swipe the credit card it says invalid data, why is that does anybody know? A few questions one WHY oh WHY PcCharge???? now to my next question, When setting up RMS does the MID match PCCharge MID? Would this processor work with the direct version of RMS? If you open notepad and swipe the card does the track data show in notepad? "TJW" <TJW@discussions.microsoft.com> wrote in message news:A5A5F465-586A-4FCB-84CB-850196CB86C2@microsoft.com... > Hi, i just install the p...

Excel 2002
I have a user who has a Workbook (which is shared, and pages are linked to each other). She opened it up this morning, and it opened as 2 "versions". The naming convention was Workbook.xls:1 and Workbook.xls:2. If she made a change to one, it was "duplicated" on the second "version". She advised all she did to the worksheet was to unfreeze the pane. She worked on the worksheet yesterday, and it did not do this. When she opened it this morning, the view was at 100% (from 75%), and Workbook "versions" were showing (and the directory where the ...

Office X #2
I'm trying to do a presentation in Powerpoint X in Chinese, but the characters are not showing up. The chinese characters show up fine in Word, but not in Powerpoint. I don't know why. The only Asian languages Microsoft seems to support are Korean and Japanese, which are of no help to me. Any suggestions? ...

cant send email #2
hi ive trying to set it up with 2 accounts one with my o2 webmail and another with universitys exchnge server account. However with my o2 account it receives email but wont send. the error message is: connection to microsoft exchange server is unavailable.outlook must be online or connected to the internet to complete the action. the exchange server wont receive any emails so i deleted the account but still the error come up...after putting settings i tested the settings and it all passed! help! In news:B0A621ED-10D2-4041-89D5-CBD216F8E8CE@microsoft.com, Adriano <Adriano@discussi...

mround #2
hi i am in the process of creating an excel markbook, and would like my averages rounded up to the nearest multiple of 5. i used this formula =mround(a2,5) and got back #name error message. help needed alcidd MROUND is part of Analysis ToolPak. It must be installed from the Office disk and then attached using Tools|Addins =ROUND(A1/5,0)*5 will also achieve what you want Best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "alcidd" <alcidd@discussions.microsoft.com> wrote in message news:5CEB5C12-0461-42DE-B1ED-170C900A16AD@microso...

Investment accounts #2
I was setting up some investment accounts and I realized that I wasn't doing it per money's suggestions specifically setting up one account for each paper statement that you receive. Because I had entered investments from two different statements, I decided to delete the account and start from scratch. So, I deleted the account that I had named Roth IRA. It is no longer in my account list. I then tried to set up an account for my Janus Roth IRA, but money will not allow me to enter one of the funds because it had been part of the deleted account, Roth IRA. It will not al...

Counting words #2
Hi I have a column with different words for a budget like car, tv, etc. There are a lot of them and in some cases there are repeats. Is there any way to count the number of unique words, without counting repeated words? Thanks a lot for you help. Hi use =SUMPRODUCT((A1:A20<>"")/(COUNTIF(A1:A20,A1:A20)+(A1:A20= ""))) -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: > Hi > > I have a column with different words for a budget like car, tv, etc. > There are a lot of them and in some cases there are repeats. > Is there any way to count the numbe...

mail merge from outlook #2
I am trying to mailmerge from outlook business contacts to a publisher newsletter that i have created. Is is possible to select certain categories from the contact and mailmerge just to those contacts ...

Outlook Web Access, 2 Servers
Good morning. I am trying to get my head around something and request input from the group. I have two exchange 2003 servers. both on Windows 2003. These two servers are in different Sites and are connected via VPN. Server A is accessable from the outside world using Outlook Web Access. if you hit a public IP we own from the outside on http it pushes through to exchange. works nice, everyone loves it. People with mailboxes on Server B would like to use this functionality as well. I do not want to open up ports on the firewall / public IP at Server B's site. Is there a way t...

VLOOKUP PROBLEM #2
I have a VLOOKUP formula in Excel 2000 that is doing what I want it to do, but I don't know that it's going to work correctly for the whole project. The short of what i am trying to do is i have a sheet that has 2 columns. the first is a vendor ID and the second is a percentage that we take off of retail price from that vendor. Basically what i would like to do is on another worksheet, be able to enter a vendor ID that will then lookup their discount percentage and display it in the cell next to it. i then do various calculations from there. this works perfectly if i only enter one ven...

creating a formula #2
I have a col that i have plus and minus numbers in them and i want a count of all the records in the col. but i want to exclude the 0 records. Currently i use =count(d3:d70) and have tried countif((d3:d71)((<>0)) with no luck. any suggestions? -- wwoody ------------------------------------------------------------------------ wwoody's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17362 View this thread: http://www.excelforum.com/showthread.php?threadid=499964 Try this: =COUNTIF(D3:D70,"<>0") HTH, Elkar "wwoody" wrote: >...

View Hotmail Junk Folder in Outlook 2003 #2
Hi- I just brought up outlook and notice that my favorite folders are reset as well as my hotmail account. I cannot see my Hotmail Junk Mail folder or any of my personal folders, even when just exploring the Hotmail folder. I tried deleting the account and adding it again but still no luck. I did just update to Office SP1 though. Does anyone else experience this or know what I might be able to do to see the folders again? Thanks! -Dan ...

Turning off some messages
Is it possible to turn off some messages? I'm looking at this from the point of view where I'm processing keyboard input within a loop and using GetAsyncKeyState. Should I be concerned with backlogged keyboard messages? Would there be an increase in efficiency if I turn off sending WM_KEYDOWN, WM_KEYUP, etc. messasges? I have no idea what it means to "process keyboad input within a loop using GetAsyncState" except that you have possibly missed the fundamental ideas of Windows. I suspect that what you are doing is a deep mistake. You should be concerned with building a Win...

Outlook 2003 Crashes constantly #2
Hi to all: I've just installed Outlook 2003 on 2 XP computers and the application crashes constantly, as the person tries to send, print, forward or reply emails, even adding attachments the application crashes and closes itself. All the patches were installed from office website. These 2 computers used to have Office 2000 previously running. I removed 2000 before installing 2003. Any ideas on what could be causing this problem?? Thanks in advance. Have you tried a new mail profile here: Control Panel->Mail Icon->Show Profiles? --� Milly Staples [MVP - Outlook] Post a...

POP3 #2
I setup MS outlook to check email from my workplace and it works very good, but now I have a problem when i go back to my office. I 'm not able to check the mail that I've already read at home. Is there anyway I can setup so that I can read email from my home and office. Thank you, Nat Nat wrote: > I setup MS outlook to check email from my workplace and > it works very good, but now I have a problem when i go > back to my office. I 'm not able to check the mail that > I've already read at home. Is there anyway I can setup so > that I can read email from my ...

A couple of questions #2
I am using Office Outlook 2003 in Windows XP. My problem is that I tried to send 3 large files, and it did not send. The 3 messages stayed in my outbox. I kept getting the "sending/receiving" box popping up, stalling at 75% and saying "1 task successfully completed", or "0 completed". I was able to delete 2 of the messages in the outbox, but the 3rd won't delete. I can't even move it to another folder. The send/receive box pops up at least 4 times a minute. The icon at the bottom says Microsoft Outlook is synchronizing folders. The error ...

Business Alert #2
I am trying to create a business alert that would alert the user (not through email) that there was a new customer or vendor or item that was created by any of the user/s. I already included the master table however, I am not quite sure on how to create the alert formula. And how does an alert looks like when I choose Alert by User ID? On May 10, 3:54 pm, PM <P...@discussions.microsoft.com> wrote: > I am trying to create a business alert that would alert the user (not through > email) that there was a new customer or vendor or item that was created by > any of the user/s. ...

Outlook Webmail mysterious ? #2
About one month ago the emails that I send from the OWA client started to prepend a "?" with a new line. i.e ? test email I have ran Adaware, Spybot and Hijack this with no joy on resolving this anomoly. I know it sounds like a possible virus but I am and have been running Mcafee with updates scheduled nightly. Anyone have any ideas, What is the best way to reinstall IE 6 sp2 on XP sp2? TFA ...

Money 2001 Deletion #2
I CAN'T DELETE/REMOVE MONEY!!!! I lost the disk and was having problems with the program so bought 2004. 2004 say's it sees the old version and I must have the 2001 disk in order for it to finish install. I stopped install and tried to remove through Windows, only to have it tell me that I need the disk. I called Customer Service, and they were completely incompetant and unable to help me do anything but realize microsoft has terrible support options. I just want to get this worthless program off my system. PLEASE HELP. I'm just going to keep calling microsoft every...