Check for three values and return other value if found

**akalien** · 08-06-2012, 04:45 AM

In range F1:F60 I will have to put once 1, 2 and 3 but the postions of those value may vary

Now I need a formula which fills all other 57 cells with a 0

**bonny24tycoon** · 08-06-2012, 05:41 AM

Can you upload an example workbook and add comments flagging your desired output..

**johnjohns** · 08-06-2012, 05:52 AM

To the extent I understood your query, try this
After typing in any 3 cells,
Select the range F1:F60
Select Ctrl+G ►Goto ►Special►Blanks
Now you got all the 57 blank cells selected
Now within the selection, type 0 and press Ctrl+Enter

**akalien** · 08-06-2012, 06:03 AM

Attached you'll find an example.

The three fields in red appear each only once but the position is not everytime the same.

All the rest in yellow should get a 0 when the 3, 2 and 1 are found in the list.

**akalien** · 08-06-2012, 06:13 AM

Great that's what I wanted to have but is there any way to do it automatically with a formula?

**bonny24tycoon** · 08-06-2012, 06:47 AM

Would a macro do??

If yes, then here you go.. Aftr entering 1, 2 or 3.. Highlight all 60 rows and then run the below macro -

Please Login or Register  to view this content.

**johnjohns** · 08-06-2012, 06:55 AM

Also you can record a macro while doing it manualy. Here is the result of my attempt

Please Login or Register  to view this content.

**bonny24tycoon** · 08-06-2012, 07:54 AM

Hi Akalien,

I understand that your query has been resolved. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.