Jump to content


Photo

How to copy and paste a range of data when criteria has been met?

range of data column data criteria

  • Please log in to reply
4 replies to this topic

#1 DKeyz

DKeyz

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 14 November 2017 - 06:02 PM

Hi Sir,

 

Can you please help me out  with the following question.

 

How to copy and paste a range of data (or column of data) when a certain criteria has been met?

 

For example:

 

In sheet 2 the criteria is "Test A". Thus hlookup in sheet 1, and when criteria value is found, then copy all data which is present below that found value (1 column of data) and paste that range of data (column with data) in sheet 2 below the criteria value (a column of data). 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 14 November 2017 - 10:11 PM

Hi DKeyz,

 

You should be able to do this by using filters. If you filter your data to the values you need, you can then copy and paste them as needed.

 

You can see how to apply filters in the video lesson: Expert Skills Lesson 1-3 Apply a simple filter to a range.

 

I hope this is the answer you were looking for, but feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 DKeyz

DKeyz

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 15 November 2017 - 09:47 PM

Good afternoon Jonathan,

 

First of all thank you for your feedback.

 

Actually, I think it is a bit more complex. 

 

Sheet 1:  is my datasheet which I want to refresh every week  with new data (just by a manually performed copy-paste action); the column headers (for examle two or more columns: "Name 1", "Name 2" etc.) will not change (only the data below all column header names will be renewed/refreshed). BUT column "Name 1" (for example) can be changed and be the fourth column when counting from left to right, and in the following week it can be the fifth column counting from left to right. So wherever column "Name 1" is positioned, i need a kind of Hlookup function in order to locate the "Name 1" column.

 

When I have found the "Name 1" column, I then want to automatically copy all data which is contained in the cells of that (respective column "Name 1" and below the column name) and paste it in another sheet  (in this case Sheet 2): this automatically copy and paste part is my problem.  

 

Sheet 2: must be my overview sheet of all columns having (copy pasted) data. And the columns will always be located at the same pre-defined fixed position. So during every refreshment of data, the columns in sheet 2 will not change from position.

 

In short: 

So actually, when I would have four different Datasheets (four different types of "Sheet 1")  then "Sheet 2" must be the sheet where all data from those four sheets are being gathered and structured in the correct columns (column names in sheet 2 will be recognizable in the four datasheets, but can be located at different column positions in the four datasheets).

Thus, I first need to fill my four datasheets with data and then search the data by using column header names, and then copy>paste (transfer)(automatical action preferred) the data which is below the respective column header names, to Sheet 2 in the correct column.

 

And then I will use sheet 2 in order to create a pivot table.

 

Sorry for the long text. :rolleyes:

 

Regards,
Donovan
 



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 16 November 2017 - 11:51 PM

Hi DKeyz,

 

I think this could be solved using Excel 2016's new Get & Transform features. If you convert your source data into a Table, you can use Get & Transform to extract the "Name 1" column and display it on Sheet 2. Because this is done with a Get & Transform query, it can be refreshed when needed, and can even be merged with other queries, allowing you to return a single set of results that combines all 4 of your datasheets.

 

Get & Transform is covered in great depth in our Excel 2016 Expert Skills course, including append queries. The 2016 course can be purchased either as a printed book or e-book from our books page.

 

I have attached an example workbook showing this in practice:

Attached File  AppendedTables.xlsx   18.45KB   79 downloads

AppendedTables.png

 

Unfortunately, the Get & Transform query won't be able to automatically adjust if your data is rearranged. You may need to manually arrange your data so that the columns that you need to extract are in the place that the query expects. From your description it's possible that this won't deliver an ideal solution, but I think it's the closest you'll be able to get to your requirements. Hopefully it is useful to you and you're able to extract the data in the form you need.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 DKeyz

DKeyz

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 17 November 2017 - 10:06 AM

Goodmorning Jonathan,

 

Thank you for your advice. It is a new feature for me to study, the  "Get & Transform" (or power query). I did download an add in for Excell 2010 and tried out your example.

It works perfectly! But I need to dive in  to it to understand how to add and link tables etc.

 

I can definitely proceed further.

 

Thank you very much!

 

Regards,

Donovan






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users