Paste multiple lines from single point of reference

[nickamongo]

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

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Ref[/TD]
[TD]Info1[/TD]
[TD]Info2[/TD]
[TD]Info3[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]wer[/TD]
[TD]asd[/TD]
[TD]uio[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]ert[/TD]
[TD]dfg[/TD]
[TD]wer[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]xxy[/TD]
[TD]bbg[/TD]
[TD]yyhj[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]wer[/TD]
[TD]asd[/TD]
[TD]ffg[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]erg[/TD]
[TD]wer[/TD]
[TD]asd[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

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.

 

[nickamongo]

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..

 

[FormR]

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
	A	B	C	D
1	Ref	123
2	Helper cell	3
3
4	Ref	Info1	Info2	Info3
5	123	wer	asd	uio
6	123	wer	asd	uio
7	123	wer	asd	uio
Sheet1

Cell Formulas
Range	Formula
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
	A	B	C	D
1	Ref	Info1	Info2	Info3
2	123	wer	asd	uio
3	456	ert	dfg	wer
4	123	xxy	bbg	yyhj
5	123	wer	asd	ffg
6	456	erg	wer	asd
Sheet2

 

[nickamongo]

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?

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]=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)))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

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.