User Posts: Masum Mahdy
0

## How to Combine Cells in Excel (4 Methods + Shortcut)

While dealing with Microsoft Excel, we often have to combine values in cells in different situations. MS Excel has numerous ways to join cells, and we will see ...

0

## Delete Blank Rows in Excel (8 Suitable Methods)

Imagine a case when you have a set of data in an Excel worksheet, and you see some unnecessary blank rows. No doubt, such unexpected empty rows annoy everyone, ...

0

## How to Copy Formula in Excel (6 Quick Methods)

While dealing with MS Excel, we often have to deal with an Excel formula, copy it to different cells, columns or rows. There are several methods to copy a ...

0

## Percentage Formula in Excel (6 Ideal Examples)

In Microsoft Excel, we can use different methods to calculate the percentage of a numeric value with a formula. We can apply algebraic calculations or insert a ...

1

## How to Use Paste Options in Excel

Paste options in MS Excel give us the opportunity to choose certain elements and keep them in the destination cells. In this article, we will show all the ...

1

## How to Transpose Rows to Columns in Excel (6 Quick Methods)

Transposing data in Microsoft Excel is truly one of the most common tasks performed by Excel users. Indeed, there are many ways for transposing row to column ...

1

## How to Merge Duplicate Rows in Excel (5 Effective Ways)

While dealing with Microsoft Excel at home and office, at super shops or corporate companies, we often need to merge duplicate rows in Excel. There are ...

1

## How to Sum Rows in Excel (9 Easy Methods)

In Microsoft Excel, the Sum is one of the most common operations we use. There are different techniques to sum up values in multiple rows in excel. Today we ...

Browsing All Comments By: Masum Mahdy
1. Thank you for your compliment, David!
Please click on the following link for more examples of the INDEX-MATCH combo:
https://www.exceldemy.com/tag/index-match-excel/

2. Hi Anil, here is an easier and shorter formula for you. (FILTER function is available from Excel 2019)
`=FILTER(\$B\$5:\$B\$10,\$C\$5:\$C\$10=F4)`
Do the following to your desired list.
>> Write “God” in cell F4 and “Human” in cell G4.
>> Copy the above formula and paste it into cell F5 (it will list God Names)
>> Similarly generate the Human Names. Just use G4 instead of F4 in the given formula.

Can you visualize what to do? Please let us know if this helps.
Regards
-ExcelDemy Team

3. Hi Shimrit, you are most welcome! We are glad to hear that it worked for you.
Regards
-ExcelDemy Team

4. Hi Haley,
We have updated the Excel workbook and added a new raw worksheet. So now you can practice from scratch. Stay with us always.
Regards
-ExcelDemy Team
Practice Workbook

5. You may find this formula useful.
`=SUM(FILTER(B2:S2,ISEVEN(COLUMN(B2:S2)))-FILTER(B2:S2,ISODD(COLUMN(B2:S2))))`
Regards
-ExcelDemy Team

6. Hi ROD, we are really sorry for your experience, there is no Excel formula to move data from one cell to another actually. So we have revised the whole write-up. Thanks for your (Bill too) valuable feedback. We hope you will be with us in the coming days as well.
-ExcelDemy Team

7. Dear HEATHER, I guess you are facing the same problem as HANNAH. But we have rechecked the code, and applied it again, but have found no issue.
We have added the code explanation. Have a look at it and let us know if it can do any help regarding your problem. But the best will be to send your problem in detail with your Excel file. Thanks and regards.
-ExcelDemy Team

8. Hi Don. Thanks for letting us know about this. đź™‚ We have updated the blog post. Regards
-ExcelDemy Team

9. Hi JIM, we are really sorry if these methods cannot help you. Yes, we agree with all of you that empty strings and blank cells are not the same in Excel. We have tried to show some ways to set cells to “seemingly blank” using Excel formulas.
Now, if you need to set cells to absolutely empty (nothing inside cells), sorry Excel formula cannot help you in this regard. But if seemingly blank is enough for you, these formulas work properly.
If you have a specific case, where you need to set cells to truly blank, then let us know at [email protected] with your problem-details and Excel file. We will definitely try to help.
Best regards.
-ExcelDemy Team

10. You are welcome, Henry. Yes, it’s possible. Please share your problem with details at [email protected]
Regards.

11. Hi AVINASH. Check your email for a sample file. I have used the INDIRECT function with the Data Validation command to make it.
Regards.

12. Hi GABOR! Please click on the following link to get a code that will help you to save selected worksheets in the list as PDF files in a suitable location.

https://www.exceldemy.com/vba-code-for-print-button-in-excel/#3_Apply_VBA_Code_to_Create_Print_Button_for_Selected_Sheets_in_Excel

Note that, you have to go through the first two steps of the first example in the given article. Then you can apply the code there. (We have mentioned it there also.)
Stay with us. Best regards.

13. Hi AKSHAY THAKKER! We hope you are well. It’s been 6 years since you posted this query here. We are extremely sorry for being so late in responding. Hope you would have had a solution to your problem somewhere by now. However, we are providing a solution to your question hoping that other readers might find it useful.
For example, we have created a 10×10 array using the following formula.

``=IF((ROW(A1:A10)-(COUNT(A1:J1)-COLUMN(\$A\$1:\$J\$1)))>1,0,(\$A\$1:\$A\$10)*(IF(A1:J1=A1:J1,1)))``

Look at the following image.

You must do two things before applying the formula.
First, place the numbers in the first row (row 1, i.e. row of A1) serially, and second, place them serially down the first column (column A). You can place them elsewhere, but in that case, you have to change the cell references in the array formula accordingly.
You can create any square array with your desired sequence (1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0) having any square dimension. However, if you want to change the sequence, you have to change the formula a bit.
Tip: Look into the greater than logic in the formula. You have to make the change here to create other sequences.

If there is any query, please let us know. You can also send us your problem at this address: [email protected]

14. You are welcome, Surya đź™‚

15. You are welcome, Ahmad đź™‚

17. You are welcome, Surya! đź™‚

18. Thank you so much, Ferreira. Visit our blog and explore more!

19. Hi Bill Coriell! Sorry for being late in reply. Maybe you were asking for the following article.
https://www.exceldemy.com/what-is-an-array-in-excel/
With regards
-ExelDemy team

20. Dear pat, sorry for replying late. Please send your file to [email protected] so that we can help. Thanks for commenting.

21. We are extremely sorry Hannah W. to know that you have faced this difficulty in applying our provided code! We have added the code explanation. Is your problem solved? Please let us know.
With regards
-ExcelDemy team

22. The code is perfectly working from our end, Goel! Can you please send your problem to this email: [email protected]?

https://www.exceldemy.com/insert-date-picker-in-excel/
If this doesn’t help, please let us know. You can also send your problem with your Excel file to this email address: [email protected]
Thanks again for being with us.
With regards
-ExcelDemy team

24. You are welcome, Ruben! Please share your thoughts with us, too. Regards.

25. Hi Pete, you are right! `Variance= ABS(new value-original value)/original value` is the correct one.
We are really grateful for your precious feedback. We have updated the Excel file and main content accordingly.

Best Regards.
-ExcelDemy team.

26. Hi Deepak!
The following formula can match two criteria and return multiple matches:
`=INDEX(\$E\$2:\$E\$14, SMALL(IF(COUNTIF(\$G\$2, \$C\$2:\$C\$14)*COUNTIF(\$H\$2, \$D\$2:\$D\$14), ROW(\$A\$2:\$E\$14)-``MIN(ROW(\$A\$2:\$E\$14))+1), ROW(A1)), COLUMN(A1))`
Here,
`\$E\$2:\$E\$14`= Column to return matches
`\$G\$2 and \$H\$2`= Criteria 1 and 2
`\$C\$2:\$C\$14` and `\$D\$2:\$D\$14`= The columns of main data respective to criteria 1 and 2
In a similar way, you can add any number of criteria.

Thank you for being with us.

27. Hi MAHENDRA TRIVEDI! You can use the following formula to compare two columns and return multiple matches in a single cell:
`=TEXTJOIN(",",TRUE,IF(A5:A15=D5,B5:B15,""))`
Here,
A5:A15= Range for matching criteria
B5:B15 = Range of the values to return
D5= Lookup value
TRUE ignores all the empty cells.

Check the 3rd case of the following article to know more details.
hhttps://www.exceldemy.com/index-match-return-multiple-values-vertically/

Thanks for being with us. Best regards.

28. Hi CLĂ‰MENTINE! You have to select the newly added data from the filter list first (mark the Select All box for that). Then the filter command will work as usual. Thanks!

29. Hi Stevoisiak! Hope you are doing well.
Suppose you have the following name in cell B5.
Mr. Brian Charles Lara
To extract all the parts of this name, use the following formulas.

To separate prefix: `=LEFT(B5,SEARCH(" ",B5)-1)`
To separate first name: `=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-(SEARCH(" ",B5)+1))`

To separate middle name: `=MID(B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1,SEARCH(" ",B5,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1)-(SEARCH(" ",B5,SEARCH(" ",B5,1)+1)+1))`
To separate last name: `=RIGHT(B5,LEN(B5)-FIND("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))`

You can check the following article too, to know more.
https://www.exceldemy.com/separate-first-middle-last-name-with-excel-formula/

Best Regards.

30. Hi DAVID BAKER! Hope you are well!
https://www.exceldemy.com/highlight-specific-text-in-excel-cell-vba/
Best Regards.

31. Hi GEOFFREY! Your problem is not quite clear to us. However, if you simply copy and paste the cell contents, the hyperlinks will also be pasted. If this is not what you were expecting, then we would request you to elaborate on your issue. You can also send your Excel file to us through email. Thank you!

32. We appreciate your nice suggestion, ANTHONY! You can also see the following article to know about all the Paste Options in Excel.
https://www.exceldemy.com/paste-options-in-excel/

Thank you for being with us. đź™‚

33. Hi Sara, the macro in #9 works fine on our part. Have you changed the Range(B5:D14) according to your dataset? If you have typed the range correctly, and still the macro is not working for the last row, please let us know.

34. Hi Rakesh! I hope this formula will do the task for you.

`=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))`

Here are more ways to extract just numbers from your data.
https://www.exceldemy.com/extract-only-numbers-from-excel-cell/

5 Excel Hacks You Never Knew