How to allocate amount of rows according to amount of rows in another sheet

Question

How do I insert amount of rows according to data from another sheet?

This is my reference data from sheet a

I have 5 data out of the 10 rows I have (I keep the empty rows for spare)

This is my expected output in sheet b

How do I make it so the data here corresponds to the amount of data in reference sheet? Such that if I have 5 names, this sheet will automatically return 5 rows where the data will be in. If I have 6 names in the reference table, this sheet will have 6 rows for the data, etc.

I'm thinking of using VLOOKUP for inserting the data needed into the cells in sheet b and using COUNTA to return '5' for the number of names present.

The main problem here is how do I automatically insert the 5 rows according to amount of names present.

Help is much appreciated.

How to populate the column Buyer, Remaining and Total? How about =If(B3="","",B3") (B3 is a cell in the reference table)? — taller
– taller, Commented Sep 4, 2023 at 4:32
But I would still need to add and remove the rows manually. I plan to protect the output sheet (sheet b) so that any changes I make in the reference sheet would automatically reflect into the output sheet. So it'll be fully automated depending on the amount of data from the reference sheet. As I also have the "Total" at the bottom of the table, it is important that the rows increase and decrease automatically — Li Jialong
– Li Jialong, Commented Sep 4, 2023 at 4:40
what do you mean about include column header? Isn't "No.", "Seller", "Buyer", etc column header? Or am I missing something here? I also have never dabbled in vba before. — Li Jialong
– Li Jialong, Commented Sep 4, 2023 at 5:06
Pls update your screenshot to include row and column heading. — taller
– taller, Commented Sep 4, 2023 at 5:13

P.b · Accepted Answer · 2023-09-04 11:38:32Z

0

Vlookup will return the first value matching the criteria. You need to use FILTER to return multiple values with the same condition:

=LET(d,'Sheet1'!D3:D12,VSTACK(FILTER(CHOOSECOLS('Sheet1'!B3:D12,1,3),d),HSTACK("Total Down Payment",SUM(d))))

Edit: I noticed both VSTACK/HSTACK and CHOOSECOLS are not part of Excel 2021 (accessable using Excel for the web though).

Excel 2021 version would be: =LET(d,D3:D12,s,SEQUENCE(ROWS(d)),a,FILTER(INDEX(B3:D12,s,{1,3}),d),b,CHOOSE(SEQUENCE(,2),"Total Down Payment",SUM(d)),IF(SEQUENCE(ROWS(a)+1)>ROWS(a),b,a))

edited Sep 4, 2023 at 11:38

answered Sep 4, 2023 at 7:29

P.b

12k2 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

taller · Accepted Answer · 2023-09-04 16:28:50Z

Seller and Down Payment are populated with data from the reference table. Populating other columns are NOT mentaioned in OP. Please update as needed.

Sub demo()
    Dim ShtA As Worksheet ' reference table
    Dim ShtB As Worksheet ' output table
    Dim RowCnt as long
    set ShtA = Sheets("Sheet1")
    set ShtB = Sheets("Sheet2")
    RowCnt = ShtA.Cells(Rows.Count,1).End(xlUp).Row - 2
    If RowCnt > 0 Then
        With ShtB
            ' Change it to "B3:F10", if Total column should be cleaned
            .range("B3:E10").ClearContents
            .Range("B3").Resize(RowCnt,1).value = ShtA.Range("B2").Resize(RowCnt,1).value
            .Range("D3").Resize(RowCnt,1).value = ShtA.Range("C2").Resize(RowCnt,1).value
        End With
    End If
End Sub

Collectives™ on Stack Overflow

How to allocate amount of rows according to amount of rows in another sheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related