0

## How to Show Excel Metadata Viewer (4 Effective Methods)

This tutorial will demonstrate how to show Excel Metadata viewer. Sometimes when dealing with lots of data, we need to know about the hidden details of an ...

0

## [Fixed] Excel Margins Not Printing Correctly (6 Possible Solutions)

This tutorial will demonstrate how to fix Excel Margins not printing correctly. In our day-to-day life, we have to print Excel worksheets with proper margins. ...

0

## How to Use MACRS Depreciation Formula in Excel (8 Methods)

This tutorial will demonstrate how to use the MACRS depreciation formula in Excel. A MACRS Depreciation Schedule is a table that displays the amount of ...

0

## How to Calculate Days Outstanding in Excel (With Easy Steps)

This tutorial will demonstrate how to calculate days outstanding in excel. When you are in the business field, it requires lots of effort to reclaim cash from ...

0

## How to Use Reference of Data Model in Excel Formula

This tutorial will demonstrate how to make an Excel reference data model in a formula. The Excel Data Model is a particular form of data table wherein two or ...

0

## How to Insert Sunburst Chart in Excel (2 Easy Examples)

This tutorial will demonstrate how to insert a sunburst chart in excel. When dealing with lots of data, we often represent the data in a chart form in business ...

0

## How to Change Comma Separator in Excel (7 Easy Methods)

This tutorial will demonstrate how to change the comma separator in excel. In our day-to-day life, we have to deal with lots of data. When we are upstaging ...

0

## How to Create Org Chart From Excel Data (With Easy Steps)

This tutorial will demonstrate how to create an org chart from excel data. An Org chart is known as an organizational chart that demonstrates the whole ...

0

## How to Make a Conversion Table in Excel (8 Easy Examples)

This tutorial will demonstrate how to make a conversion table in excel. In our day-to-day life, we need to convert different units. Sometimes we need to ...

0

## How to Create a Formula with Structured References in Excel

This tutorial will demonstrate how to create a formula with structured references in excel. When you are dealing with lots of datasets, you need to select ...

0

## How to Use COUNTIF Function in Excel Greater Than Percentage

This tutorial will demonstrate how to use the COUNTIF function greater than the percentage in Excel. One of the most useful functions of Microsoft Excel is the ...

0

## How to Translate Arabic to English in Excel (With Easy Steps)

This tutorial will demonstrate how to translate Arabic to English in excel. In this modern era, global communication and trading have become very important. ...

0

## [Fixed!] Merge Cells Button Is Greyed Out in Excel

This tutorial will demonstrate how to fix the merge cells button that is greyed out in Excel. One of the most general uses utilized by beginner excel users is ...

0

## How to Copy a Formula in Excel Without Changing Cell References

This tutorial will demonstrate how to copy a formula in excel without changing cell references. We usually need to apply the same formulas while dealing with ...

0

## How to Make Cumulative Percentage Polygon in Excel

This tutorial will demonstrate how to make a cumulative percentage polygon in excel. The percentage polygon consists of a line chart drawn by connecting the ...

1. Reply Zehad Rian Jim Aug 17, 2022 at 12:39 PM

Hello DESTINY,
First, thanks for your curious question. It was amusing to solve the problem. Let me guide you to fulfill your query.

Step 1. Assume you have a Dataset where you have the Names of the employees in one column and the types of the employees in another. Step 2. Then insert the following code in the VBA window.

Sub Copy_Rows_3()
Dim r1 As Range, Row_Last As Long, sht As Worksheet
Dim Row_Last1 As Long
Dim src As Worksheet
‘Change this to the sheet with the data on
Set src = Sheets(“Dynamic”)
Row_Last = src.Cells(Cells.Rows.Count, “C”).End(xlUp).Row
For Each r1 In src.Range(“C5:C13” & Row_Last)
On Error Resume Next
Set sht = Sheets(CStr(r1.Value))
On Error GoTo 0
If sht Is Nothing Then
‘Sheets(CStr(r.Value)).Cells(1, 1) = “Total”
Row_Last1 = Sheets(CStr(r1.Value)).Cells(Cells.Rows.Count, “B”).End(xlUp).Row
src.Rows(r1.Row).Copy Sheets(CStr(r1.Value)).Cells(Row_Last1 + 1, 1)
Sheets(CStr(r1.Value)).Cells(1, 2) = WorksheetFunction.Sum(Sheets(CStr(r1.Value)).Columns(3))
Set sht = Nothing
Else
‘Sheets(CStr(r.Value)).Cells(1, 1) = “Total”
Row_Last1 = Sheets(CStr(r1.Value)).Cells(Cells.Rows.Count, “B”).End(xlUp).Row
src.Rows(r1.Row).Copy Sheets(CStr(r1.Value)).Cells(Row_Last1 + 1, 1)
Sheets(CStr(r1.Value)).Cells(1, 2) = WorksheetFunction.Sum(Sheets(CStr(r1.Value)).Columns(3))
Set sht = Nothing
End If
Next r1
End Sub

N.B. if you are following our article, use the VBA code under the method “Copy Rows in Excel to Another Sheet Dynamically” and change the marked portions. Step 3. After pressing Run, you will get the result in individual desired cells.  Thanks

2. Reply Zehad Rian Jim Sep 5, 2022 at 11:13 AM At first, Create a dataset having Age in Column B, Fixed Amount in Column C and Principle At the Start of the period in Column D.
Then, insert the following formula in cell D5 and use the Fill Handle option to apply it to all cells of column D.
=B5*C5 Finally, insert the following formula in cell E6 and use the Fill Handle option to apply it to all cells of column E to get the desired result.
=E5*(1+0.02)+D6 Compound Interest Statement.xlsx
Thanks and happy helping.

3. Reply Zehad Rian Jim Sep 23, 2022 at 1:01 AM

Hello CALEY FORBES,
Thanks for the amazing question. Let me guide you in solving this problem.

First, I want to solve your first query. I think to solve your problem it is better to use our first method ‘Automatically Copy Rows in Excel to Another Sheet Using Filters’ method than using VBA code. The reason behind this is I think the first method will do your desired work without hesitation.

For the second query, you have to insert the following formula in the VBA windows.

Sub Cut_Range_To_Clipboard()
Range(“B4:B10”).Cut ‘This will cut the source range and copy the Range “B4:B10” data into Clipboard
‘Now you can select any range and paste there
Range(“J2”).Select
ActiveSheet.Paste
End Sub

Note: in the Range section you can change the desired option to paste accordingly.

I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]
Happy Excelling!!!

4. Reply Zehad Rian Jim Nov 14, 2022 at 11:42 AM

Thanks Deep for your excellent and thoughtful question. Let me guide you to fulfill your query.
We can easily extract multiple texts in cells by using different methods of this article but with slight changes.

Suppose you have a dataset where the texts are separated only with hyphens. In this scenario, you should follow the first method in our article. The steps are:
First, arrange the dataset where texts are separated with hyphens. Second, insert the following formula.
`=MID(B5,FIND("-",B5)+1,LEN(B5))` Third, after pressing Enter button, you will get the result for this cell.
Last, use the Fill Handle to apply it to all Cells. But in case, you have emails separated with @ or any other texts separated with special characters then you can use RIGHT, SEARCH & SUBSTITUTE Functions or LEFT, FIND & SUBSTITUTE Functions or RIGHT, REPT & SUBSTITUTE Functions from our article.
Any of these methods will do the work for you. Let me guide you in detail with the steps.
Firstly, you must arrange a dataset where multiple texts are separated with special characters. Next, use any of the following formulas in the D5 cell(described in our article)
`=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,C5,"#",LEN(B5)-LEN(SUBSTITUTE(B5,C5,"")))))`
or
`=SUBSTITUTE(B5,LEFT(B5,FIND(C5,B5)),"")`
or,
`=TRIM(RIGHT(SUBSTITUTE(B5,C5,REPT(" ",LEN(B5))),LEN(B5)))`

(Note: Please take a glimpse at our main article to understand the insertion of the formula)

Afterward, after pressing Enter button, you will get the result for this cell. You will get the same result for any of the formulas so you can choose any of them.
Last, use the Fill Handle to apply it to all Cells. 5. Reply Zehad Rian Jim Nov 27, 2022 at 1:10 PM

First, we are going to discuss your first question. The problem you faced is about custom sorting.
At the very beginning, we arranged a dataset of change in percentage. Then, as you wished we have used the COUNTIF function in this dataset. As a result, we have found the values. Now, select Dataset> go to Data>Sort options. After that, in the Sort window, select Change% in Sort By option, Cell Values in Sort On option, and Custom List in the Order option. Press OK to execute it. Furthermore, click on the NEW LIST option and write the condition manually in the List Entries area and press Add option. Because of that, you will get the desired condition in the Custom Lists section and press OK. Afterward, if you can see the desired condition in the Order box then press OK. Lastly, you will get the result accordingly. As in the condition, you have entered less than zero at first then zero to ten and at last greater than zero that’s why in the Excel section you will get the same order accordingly. So, that’s the solution to your first query. Now, Let’s go through your second problem.
First, by reading your example, at first by using a formula, we created a Uniques Names list. Second, you have to write down the same unique names list in another column. The behind this is, in the array, you can’t use sorting. Third, then enter the Observations you made next to the new column. Forth, as we want to sort, so we mark each name with a unique Id number in a new column. Fifth, go to select the table>Data>Sort options. Sixth, select the Id option in the Sort by option, and Cell Values in the Sort On option, click on the Largest to Smallest option in the Order section, and press OK. Finally, you will get the desired result. So, this is the solution to your second query.
Therefore, our journey comes to an end. The problems were very fun to solve and I really feel amazed by helping you. Thank you once again. Best of luck.

6. Reply Zehad Rian Jim Nov 27, 2022 at 3:19 PM

First, we arranged a dataset and add an extra column(in this case Sales) in the same table as the below image. Then, insert the following formula in cell E5.
=PRODUCT(C5,D5) After that, if you press the Enter button, then you will get the result for that cell, and afterward, use the Fill Handle option to apply the formula to all cells. Finally, you will get the desired result. So, I have tried to solve the problem to multiply the cells in the same table. If you face any other confusion, then we request you to provide the Excel file and give us the opportunity to help you out. All the best.  5 Excel Hacks You Never Knew  