יום שישי, 1 ביולי 2022

How to remove empty rows in a range (without VBA, without PQ, without formulae, even without the mouse)

 

Excel – How to remove empty rows in a range

 

The following technique explains, step by step, how to get rid of empty rows within a range.

Please note that this technique does not use either VBA, PQ or formulae.

It doesn't even use the mouse 😊






יום שני, 13 ביוני 2022

How to get rid of unwanted names in a list

 

How to get rid of unwanted names in a list

(no formulae, no PQ)

Suppose you have a list of names (in col. A) from which you need to exclude names that appear in a different list (col. C)

 

This can be easily done in a few simple steps without formulae or Power Query:

 

A .  we prepare two lists

In col. A – the original list

In col. C – the list of names to exclude from the original list.

                                                    Pic No.1


 

B .  we "color" in yellow all the names in col. A

                                                            Pic No.2

 

 

C.  Find duplicates using Conditional Formatting
select both columns-> Conditional Formatting-> Highlight Cells Rules->Duplicate Values

 

 

                                                Pic No.3

 

D. Filter the list in col. A and select: Filter by cell color.

You should, of course, select the yellow color




                                                     Pic No.4

E. Final Result: now the list in col. A contains only names that do not appear in the list of col. C

 


                                                                Pic No.5

                 

 



 

 

איך נפטרים משמות לא רצויים ברשימה

 

איך נפטרים משמות לא רצויים ברשימה (שיטה מקורית שלי)
(בלי נוסחא ובלי Power Query) !!!

א.    מכינים שתי רשימות (כמו בתמונה):
בעמודה
A – הרשימה המקורית
בעמודה
C – רשימת השמות למחיקה מהרשימה המקורית

תמונה מס.1

ב.     צובעים בצהוב את כל השמות בעמודה A

תמונה מס.2

 

 

ג.      מוצאים כפילויות בעזרת "עיצוב מותנה" (Conditional Formatting)

                                                                     תמונה מס.3

ד.     מפעילים סינון על הרשימה בעמודה A ובוחרים: סינון לפי צבע (Filter by color)
בוחרים, כמובן, בצבע הצהוב

תמונה מס.4

 

ה.    התוצאה הסופית: בעמודה A מופיעים רק השמות שלא מופיעים ברשימה בעמודה C


תמונה מס.5

 








יום שישי, 27 במאי 2022

Four methods to highlight the smallest number in a range

 

4 Methods to highlight the smallest number in a range

 

In Excel, as you might know, there is (almost) always an alternative method to accomplish a certain task.

In this post I’m going to show your 4 different methods to highlight the smallest number in a range:

 


                                                        Method 1: Using Top/Bottom Rules

 

 


Method 2: Using a formula (MIN function)

 

 

Method 3: Using a formula (SMALL function)

 

 

 

          Method 4: Using a formula (LARGE function)

 

 








יום שלישי, 24 במאי 2022

Finding Unique values in VBA

 

VBA  - Finding Unique values

 The problem

Suppose you have a range of cells and you want to count the number of unique values in these cells.

For example, in this picture:

                                 Pic. No.1: A range of strings

 

Using the new UNIQUE function (Excel 365, available also in VBA) will not supply us with the correct answer, since the UNIQUE function isn’t case-sensitive. It won’t recognize the differences between the three cells.


The solution: Using the Dictionary 

 I’m not going to explain the Dictionary feature in VBA.

An excellent explanation (with examples) can be found here:
https://excelmacromastery.com/vba-dictionary/

              

Before running the code, we need to add the Microsoft Scripting Runtime library by using: Tools*References in VBA’s main menu

                     Pic. No. 2: Adding the Dictionary Library (stage 1)

 

  

In the Referenced list, select the Microsoft Scripting Runtime and press: OK

  

                  Pic. No. 3: Adding the Dictionary Library (stage 2)

 

Now you can run the code:

                     Pic. No. 4: Using the Dictionary in VBA

 

As can be seen, using this technique yields the correct answer.
There are 3 unique values in the referenced cells.


Liked the post?
please give it a "Like".









 

יום חמישי, 12 במאי 2022

לוח הכפל -הדור החדש



לוח הכפל -הדור הבא

 

עד לאחרונה, הפוסטים הפופולריים ביותר בבלוג שלי היו אלו שהיו מוקדשים להסבר על יצירת לוח הכפל ב-3 שיטות שונות:

 

https://meniporat.blogspot.com/2013/07/blog-post_8817.html
)
שמוש בכתובת מעורבת(

http://meniporat.blogspot.com/2013/07/blog-post_8935.html
 (שמוש בפונקציה:MMULT )

http://meniporat.blogspot.com/2013/07/blog-post_2151.html

(שמוש בפונקציה:PRODUCT)

אבל עכשיו, כשמיקרוסופט הכניסה את פונקציות המערך הדינמי (dynamic array functions) באקסל 365, הכל יותר קל ויותר פשוט.

הטכניקות היותר מיושנות הופכות למיותרות...
"כך חולפת תהילת העולם" (Sic transit gloria mundi)


התמונה הראשונה מציגה את לוח הכפל תוך שמוש בפונקציה האהובה עלי:  SEQUENCE, פונקצית מערך דינמי שהיא חלק מפונקציות המערך החדשות באקסל 365.

הארגומנטים בתאים: Q1 ו- Q2 מאפשרים לנו להגדיר לוח כפל דינמי. אפשר להרחיב אותו או לצמצם אותו, לפי צרכיך:

אפשר, למשל, להתחיל בטבלה של 3*3, ולהרחיב בהדרגה ל: 5*5, 7*7, 10*10.... כדי להתאים את גודל הטבלה לצרכים של התלמיד שלך.

כפי שאפשר לראות, ישנן שלוש נוסחאות בתמונה:

A2 הגודל האופקי של הטבלה (מספר השורות)

B1 הגודל האנכי של הטבלה (מספר העמודות)

B2 הנוסחא שיוצרת את לוח הכפל

 

תמונה מס. 1: לוח כפל רבועי

 

אינך חייב ל"הצמד" ללוח כפל רבועי, שבו מספר השורות שווה למספר העמודות.

אפשר להשתמש באפשרות השניה:
לוח כפל מלבני, שבו הממדים האפקי והאנכי אינם זהים.

Q1 יכיל את הגודל האפקי ו Q2 יכיל את הממד האנכי.

 

 

תמונה מס. 2: לוח כפל מלבני

 

 


 

  

יום שבת, 7 במאי 2022

Another one bites the dust….

 


Another one bites the dust….


For some years now, the most popular posts on my blog were dedicated to explaining how to create the multiplication table in Excel (in Hebrew)
There are 3 posts on that subject, demonstrating 3 different methods:
https://meniporat.blogspot.com/2013/07/blog-post_8817.html
(using mixed references)

http://meniporat.blogspot.com/2013/07/blog-post_8935.html
(with the MMULT function)

http://meniporat.blogspot.com/2013/07/blog-post_2151.html

 (with the PRODUCT function)

But now, with the introduction of the dynamic array functions in Excel 365, everything is so much easier and simpler.
Alas, the old techniques are now obsolete…

“Sic transit gloria mundi” (which in Latin, means: “that is how the world’s glory vanishes”)


Consider the following multiplication table (mt) using my favourite function SEQUENCE, introduced in Excel 365.

The arguments in cells Q1 and Q2 enable you to define a dynamic mt. you can contract it or expand it according to your needs, starting, for instance, with a 3*3 mt, and then gradually increase it to 5*5, 7*7, 10*10… adapting the size to your son’s/daughter’s/pupils’ needs.

There are 3 formulae in the sheet:
A2: the horizontal size

B1: the vertical size

B2: the formula that creates the mt.

 

You don’t have to stick to the square mt, where the horizonal and vertical dimensions are identical.

You can revert to option # 2: a rectangular mt, where the horizonal and vertical dimensions are NOT identical.

So, Q1 will contain the horizontal size whereas in Q2 we will the vertical size.

See attached pictures.

                        Picture 1: The Square Multiplication Table

 

Picture 2: The Rectangular Multiplication Table