How to add Rows automatically based on Cell Value In Excel?

Solved
Raj_2823
Posts
5
Registration date
Monday August 29, 2022
Status
Member
Last seen
September 21, 2022
- Updated on Aug 30, 2022 at 12:48 AM
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
- Sep 12, 2022 at 11:32 AM

Hi All,

I have a requirement to add the Rows Based On Cell Value In Excel. Below I have attached the snapshot of the requirement details.

I have two sheets in excel. "Sheet 1" is static, and "Sheet 2" is dynamic. The Sheet 2 values will change based on system transactions. Here my requirement is to bring all those values in sheet 1.

I described the requirement in "Sheet 3" and I am expecting the output to look like Sheet 3. Can you please help me with how to add the Rows Based On Cell Value In Excel?

Regards,

Raj

2 replies

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Aug 30, 2022 at 12:06 PM

Hi Raj,

The code below does the following:

When you confirm the value in Sheet 2 column E, the value from column A will be looked up in Sheet 1 column A. The row (Column A:E) from Sheet 2 will be copied and inserted below the found value of Sheet 1, without the values from columns A and B.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("E")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Dim mFind As Range

Set mFind = Sheets("Sheet 1").Columns("A").Find(Target.Offset(0, -4).Value)

If Not mFind Is Nothing Then
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "E")).Copy
    If Sheets("Sheet 1").Range("A" & mFind.Row + 1).Value = vbNullString _
    And Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown).Row < 1000000 Then
        With Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown)
            .Insert
            .Offset(-1, 0).ClearContents
            .Offset(-1, 1).ClearContents
        End With
    Else
        With Sheets("Sheet 1").Range("A" & mFind.Row + 1)
            .Insert
            .Offset(-1, 0).ClearContents
            .Offset(-1, 1).ClearContents
        End With
    End If
End If
End Sub

Best regards,

Trowa


1
Raj_2823
Posts
5
Registration date
Monday August 29, 2022
Status
Member
Last seen
September 21, 2022

Updated on Aug 31, 2022 at 02:01 AM

Hi Trowa,

Thank you so much for your prompt response.

It's working fine as I expected. But in this logic, I should enter the value by manual in Sheet 2 column E. Is there any possibility to add the values automatically in excel?

When I open the excel the value will be transferred to sheet 1 from Sheet 2. I have inserted the add-on in excel. So, the value will update sheet 1 and sheet 2 when I open the excel file.

Here the user is not able to make any of the changes in Sheet 1 & Sheet 2.

Regards,
Raj

0
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Sep 1, 2022 at 11:33 AM

Hi Raj,

When you open the file, and the rows from Sheet 2 will be copied to Sheet 1, will Sheet 2 be cleared? In other words, do we need to be wary of duplicates?

The code below copies the rows from Sheet 2 to Sheet 1, when you open the file. Sheet 2 will be left untouched for you to see if result is correct. Note that this method will create duplicates when Sheet 2 isn't cleared upon re-opening your file.

Here is the code, which needs to be placed under ThisWorkbook):
 

Private Sub Workbook_Open()
Dim mFind As Range

Sheets("Sheet 2").Select
For Each cell In Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
    Set mFind = Sheets("Sheet 1").Columns("A").Find(cell.Value)
    If Not mFind Is Nothing Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "E")).Copy
        If Sheets("Sheet 1").Range("A" & mFind.Row + 1).Value = vbNullString _
        And Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown).Row < 1000000 Then
            With Sheets("Sheet 1").Range("A" & mFind.Row).End(xlDown)
                .Insert
                .Offset(-1, 0).ClearContents
                .Offset(-1, 1).ClearContents
            End With
        Else
            With Sheets("Sheet 1").Range("A" & mFind.Row + 1)
                .Insert
                .Offset(-1, 0).ClearContents
                .Offset(-1, 1).ClearContents
            End With
        End If
    End If
Next cell
End Sub

Let us know if further alterations are desired.
 

Best regards,

Trowa

0
Raj_2823
Posts
5
Registration date
Monday August 29, 2022
Status
Member
Last seen
September 21, 2022
> TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022

Sep 2, 2022 at 08:22 AM

Hi TrowaD,

Thank you very much for your quick response to my request.

Sheet 1 is the static value. It will not change. But Sheet 2 is dynamic. So, The value and rows are will be increased based on the transaction each day.


I would like to bring the sum of the company and transaction value based on currency. Is it possible to bring the summation value and the "Balance Company Currency" always USD. 

Please ignore the previous snapshot.

I have attached the updated screenshot below for your reference and the excel file as well (In the excel file dummy data only I entered).

I don't think this is possible to get the desired output. I have tried a lot of options with excel format. But I can't.

https://ln5.sync.com/dl/a5dd85b90/u9tgiqkn-kwgasdv5-kgzka4ze-2627it5m

Regards,

Raj

0
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523 > Raj_2823
Posts
5
Registration date
Monday August 29, 2022
Status
Member
Last seen
September 21, 2022

Sep 5, 2022 at 12:15 PM

Hi Raj,

As long as I understand what you are trying to achieve and there is logic for Excel to process your data, I'm sure we can work something out.

I'm just having trouble with finding the logic.

Looking at the yellow part, column D result you have: 100 USD, -50 ILS. Where I would expect it to be: 100 USD, 50 ILS.
Orange part makes sense.
Grey part column D, you have 16,52 EUR, -100.096.000 ILS. Where I would expect: 16,52 EUR, (-100.096.000-16,52) ILS.
Grey part column G, you have 2500 USD. Where I would expect: 500 USD.
Green part column D, here you mention a zero value not mentioned before, do you want zero values to show or not?
Green part column E, ILS changed to USD. Is that what you meant by:'the "Balance Company Currency" always USD'?
Also cell A9 of sheet2 is X11112, while the rest of the orange values are X11111, typo?

Please shine some logical light on these points.

Best regards,
Trowa
 

0
Raj_2823
Posts
5
Registration date
Monday August 29, 2022
Status
Member
Last seen
September 21, 2022
> TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022

Updated on Sep 6, 2022 at 11:28 PM

Hi Trowa,

Thank you very much for this.

Please see my comment in every line within the bracket.

1.) Looking at the yellow part, column D result you have: 100 USD, -50 ILS. Where I would expect it to be: 100 USD, 50 ILS.
The orange part makes sense. - (In the Output sheet the value 100.00 USD and -50.00 ILS comes from sheet 2 based on currency code (e.g: Column G (ILS): 100.00 and - 50.00 = 50.00 ILS )So, the 50.00 ILS I displayed in Output sheet Column D near ILS currency. 

2. Does) Orange part makes sense? - ( Yes, The same logic applied to all the Columns).

3.) Grey part column D, you have 16,52 EUR, -100.096.000 ILS. Where I would expect: 16,52 EUR,(-100.096.000-16,52) ILS? - (Based on currency code the values are summed up from sheet 2 to Output sheet to the respective columns).

4.) In Green part column D, here you mention a zero value not mentioned before, do you want zero values to show or not? - (This is not an issue, If we can able to see the 0.00 value in an empty column is fine otherwise this is not required (Displaying the 0 ILS in the column is a typo).

5.) Green part column E, ILS changed to USD. Is that what you meant by:'the "Balance Company Currency" always USD'? -(Sorry! in column E, ILS changed to USD is a typo, and the "Balance Company Currency" currency code is always USD.

Also, cell A9 of sheet2 is X11112, while the rest of the orange values are X11111, typo? - (Yes, it's a typo).

Thank you in advance!

Regards,
Raj

0
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523 > Raj_2823
Posts
5
Registration date
Monday August 29, 2022
Status
Member
Last seen
September 21, 2022

Sep 12, 2022 at 11:32 AM

Hi Raj,

How about using formula's? For sheet1 D9 use:
=SUMIF(Sheet2!$F$5:$F$8,"USD",Sheet2!G$5:G$8)

When inserting a row on sheet2, the formula will automatically adjust.

Drag the formula one cell down and replace USD with ILS. Copy both cells and select, while holding down CTRL, F9:F10, H9:H10 etc. and paste the formula.

For sheet1 E9 use:
=SUM(Sheet2!J$5:J$7)


Best regards,
Trowa

1