How to copy specific columns to other sheet in office 2021 without vba with some filter and format..

[pankajgrover]

Hi i want to copy specific columns to other sheet in office 2021. here is 3rd column name transaction type in AMZ sheet. which i want filter only "shipment" and copy corresponding specific columns in sheet1 without vba. i also used formula =AMZ!A2 & "" to copy columns but i do not know how filter works , because i want values that match to "shipment". also i want when copy date column, date should be DD-MM-YYYY FORMAT in target sheet. thanks
source sheet

Amazon_Sales.xlsx
	A	B	C	D	E	F
1	Invoice Number	Invoice Date	Transaction Type	Order Id	Shipment Id	Quantity
2	IN-13	28/02/2024 23:55	Shipment	404-8351276-2411569	A084598828TXJ9ZJJKVWO	2
3		07/03/2024 11:39	Cancel	408-1129685-2166757	A00703522BRIHLXCC8QL7	1
4	IN-16	05/03/2024 22:19	Shipment	171-1376367-6624335	A10236732OFQWNH1DULJG	1
5	IN-15	05/03/2024 18:53	Shipment	408-9509727-4152303	A012408719AAGUK57EMUK	1
6		07/03/2024 11:39	Cancel	407-2782749-1217905	A01718392OEH1RJHOZYFW	1
7	IN-14	05/03/2024 17:20	Shipment	406-2979068-9553944	A06664642D55BH8F06TTB	1
8	IN-18	07/03/2024 11:48	Shipment	408-1129685-2166757	A00703522BRIHLXCC8QL7	1
9		07/03/2024 11:39	Cancel	402-6409050-1440357	A00997353249FX3HD82QO	1
10	IN-19	07/03/2024 12:05	Shipment	406-0968836-6761124	A0787165ZD0QY6IQXRGF	1
AMZ

target sheet

Amazon_Sales.xlsx
	A	B	C
1	Invoice Number	Invoice Date	Quantity
2
3
4
5
6
7
8
9
10
Sheet1

 

[dmt32]

Hi,

You should be able to do what you want using advanced filter without any VBA.

Try following this guidance & see if will do what you want

1 – Add a criteria range in your target sheet & specify the filter criteria

Book1
	A	B	C	D	E	F	G	H
1	Invoice Number	Invoice Date	Quantity					Transaction Type
2								Shipment
3
4
Sheet1

2 – With the target sheet ACTIVE, click Data > Advanced as in screen shot

3 - In the Advanced Filter dialog box, select Copy to another location from the Action section.

4 - click button to select the entire data range which you want to filter in the AMZ sheet which MUST include the header row.

5 - click the button beside the Criteria range and select the criteria range from your target sheet which also must include the header row.

6 – click the button beside the Copy to box and in the target sheet, select the ENTIRE header row range (Columns A-C)

7 - click OK button, the filtered result should be extracted into your target sheet based on the filter criteria. You may need to manually format date column to meet specific format requirement.

Dave

 

[pankajgrover]

Hi,

You should be able to do what you want using advanced filter without any VBA.

Try following this guidance & see if will do what you want

1 – Add a criteria range in your target sheet & specify the filter criteria

Book1
	A	B	C	D	E	F	G	H
1	Invoice Number	Invoice Date	Quantity					Transaction Type
2								Shipment
3
4
Sheet1

2 – With the target sheet ACTIVE, click Data > Advanced as in screen shot

View attachment 109674

3 - In the Advanced Filter dialog box, select Copy to another location from the Action section.

4 - click button to select the entire data range which you want to filter in the AMZ sheet which MUST include the header row.

5 - click the button beside the Criteria range and select the criteria range from your target sheet which also must include the header row.

6 – click the button beside the Copy to box and in the target sheet, select the ENTIRE header row range (Columns A-C)

7 - click OK button, the filtered result should be extracted into your target sheet based on the filter criteria. You may need to manually format date column to meet specific format requirement.

Dave

how this will be filter name "shipment". i want to copy shipment corresponding cells. can we use (vlookup) or (choose) function to perform this...

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Invoice Number	Invoice Date	Quantity
2	IN-13	28/02/2024	2
3	IN-16	05/03/2024	1
4	IN-15	05/03/2024	1
5	IN-14	05/03/2024	1
6	IN-18	07/03/2024	1
7	IN-19	07/03/2024	1
8
Sheet4

Cell Formulas
Range	Formula
A2:C7	A2	=LET(f,FILTER(AMZ!A2:F100,AMZ!C2:C100="Shipment"),INDEX(f,SEQUENCE(ROWS(f)),{1,2,6}))
Dynamic array formulas.

 

[pankajgrover]

How about

Fluff.xlsm
	A	B	C
1	Invoice Number	Invoice Date	Quantity
2	IN-13	28/02/2024	2
3	IN-16	05/03/2024	1
4	IN-15	05/03/2024	1
5	IN-14	05/03/2024	1
6	IN-18	07/03/2024	1
7	IN-19	07/03/2024	1
8
Sheet4

Cell Formulas
Range	Formula
A2:C7	A2	=LET(f,FILTER(AMZ!A2:F100,AMZ!C2:C100="Shipment"),INDEX(f,SEQUENCE(ROWS(f)),{1,2,6}))
Dynamic array formulas.

Hi @Fluff sir. its working so good. Thanks.. Also guide me i add another 1 column say 15 number column. suppose in above formula INDEX(f,SEQUENCE(ROWS(f)),{1,2,6,15})) but in this 15 number column some cells are blank and some are fill with values in amz sheet. this formula showing results blank cells with 0. can we do blank cells should be blank instead of zero.. thanks

 

[Fluff]

How about

=LET(f,FILTER(AMZ!A2:O100,AMZ!C2:C100="Shipment"),x,INDEX(f,SEQUENCE(ROWS(f)),{1,2,6,15}),IF(x="","",x))

 

[pankajgrover]

How about

=LET(f,FILTER(AMZ!A2:O100,AMZ!C2:C100="Shipment"),x,INDEX(f,SEQUENCE(ROWS(f)),{1,2,6,15}),IF(x="","",x))

its perfect and good working..Thanks

 

[Fluff]

You're welcome & thanks for the feedback.