Paste multiple lines from single point of reference

nickamongo

New Member
I want to copy multiple lines from one sheet that have the same reference number into another sheet, any ideas how? e.g. all the rows that have the ref: 123

 Ref Info1 Info2 Info3 123 wer asd uio 456 ert dfg wer 123 xxy bbg yyhj 123 wer asd ffg 456 erg wer asd

<tbody>
</tbody>

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
any ideas how?

Hi, welcome to the forum!

Is it a one-off, or are you looking VBA or a dynamic formula or something else?

For a one-off try using an autofilter and copy and paste.

Hi!

It's gong to be a recurring thing whereby it needs to return all rows (or specific data from all rows) with that ref. I am not great with VBA, l'll be honest..

Here is a formula that you can try to adapt to your actual set-up. In this example, the ref you want to return the rows for is in cell B1 and cell B2 has a formula that is used to aid performance.

Excel 2013/2016
ABCD
1Ref123
2Helper cell3
3
4RefInfo1Info2Info3
5123werasduio
6123werasduio
7123werasduio
Sheet1
Cell Formulas
RangeFormula
A5=IF(ROWS(A\$1:A1)>\$B\$2,"",INDEX(Sheet2!A\$1:A\$1000,AGGREGATE(15,6,(ROW(Sheet2!\$A\$1:\$A\$1000)-MIN(ROW(Sheet2!\$A\$1:\$A\$1000))+1)/(Sheet2!\$A\$1:\$A\$1000=\$B\$1),1)))

Excel 2013/2016
ABCD
1RefInfo1Info2Info3
2123werasduio
3456ertdfgwer
4123xxybbgyyhj
5123werasdffg
6456ergwerasd
Sheet2

Great thanks! I'll give that a go. Just for my own peace of mind, can you explain the initial Argugment?

IF(ROWS(A\$1:A1)>\$B\$2,

Not sure what the helper cell is or what the rows are looking at?

Worksheet Formulas
CellFormula
A5=IF(ROWS(A\$1:A1)>\$B\$2,"",INDEX(Sheet2!A\$1:A\$1000,AGGREGATE(15,6,(ROW(Sheet2!\$A\$1:\$A\$1000)-MIN(ROW(Sheet2!\$A\$1:\$A\$1000))+1)/(Sheet2!\$A\$1:\$A\$1000=\$B\$1),1)))

</tbody>

<tbody>
</tbody>

can you explain the initial Argugment?

Hi, these types of formula are generally quite resource hungry so we want to only perform their calculations when we have to.

The COUNTIFS() function in B2 tells us how many rows we need to retrieve for the reference of interest.

IF(ROWS(A\$1:A1)>\$B\$2,

This compares the number of rows the formula has been copied down and only performs the expensive calculation when it's no more than the number of references we need to retrieve.

Replies
11
Views
642
Replies
5
Views
909
Replies
3
Views
547
Replies
12
Views
2K
Replies
3
Views
1K

1,203,521
Messages
6,055,885
Members
444,830
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back