Copy and Paste with a one to many relationship

**ExcelNoob243** · 06-18-2015, 07:20 PM

Hi,

I am looking at transforming individual lines in excel to a more tabular format. The example I have attached has the inputs for the columns that need to be copy and pasted from the DataFinal page to the ProcessInformation page.

The complication is that there is a one to many relationship. So a function can have 1 or more business processes. Each process can be identified in the header of the data. For example, when looking at Revenue Impacts, I specified that the rows IT to JG need to be copy and pasted. For the first survey taken, IT to IZ is for process 1 and JA to JG is for process 2. Additionally, the second row only has 1 business process.

Is there a way to code so that each business process gets it own line with its own data it is associated with?

Thanks!

ProcessInformationExcel.xlsx

**FlameRetired** · 06-18-2015, 11:32 PM

I'm going out a limb a bit but I suspect we could make short work of this if the column headers in the DataFinal sheet were exactly the same as those in the target sheet Process Information. Just briefly scanning your workbook I am hopeful, but I can't be certain right now.

But with 664 columns in DataFinal and 54 in Process Information of mismatched information ........

If you can take care of this and re-upload I would be willing to have another look. It appears to be a challenging project.

**ExcelNoob243** · 06-19-2015, 12:06 AM

Well that is what I am using the input sheet for. It specifies which columns of data I need from the big data sheet. So for example, the function is found in column BC as specified in the inputs sheet and would be copied into the process information sheet to column B. I know there are a lot of columns of data, but for this example I only need a few of them copied over.

**ExcelNoob243** · 06-19-2015, 12:07 AM

Additionally, I don't want to modify the data sheet because that's an export from another program so I would rather use the inputs sheet to "make the headers" match. Does that make sense? Or am I missing your point?

**FlameRetired** · 06-20-2015, 01:45 AM

Yeah I am A LOT better at Index/Match, but VBA is needed for this (not my expertise).

Originally Posted by ExcelNoob243

Additionally, I don't want to modify the data sheet because that's an export from another program so I would rather use the inputs sheet to "make the headers" match. Does that make sense? Or am I missing your point?

I've done just that to see if this might inspire a formula approach that can be used elsewhere in the workbook (-- say a duplicate of DataFinal)

Here's what I've done.

I inserted 3 "header" rows in DataFinal. See if any of those might me useful. They are array-entered formulas and resource hungry, but if the output is useful you can of course copy/paste values.

There are two helper columns in InputsFinal. These provide upper and lower column number limits to those formulas in DataFinal. Those formulas are INDEXed to yield the Survey Parts names in their respective places (1st row), their relative row position in Survey Parts (2nd row) and the column numbers of each range of Survey Parts in DataFinal (3rd row). Once again they can be copied and pasted as values...ditto the helper columns in InputsFinal.

Here is a segment of DataFinal that shows what I am talking about. It's all in the file as well.

Row\Col	IP	IQ	IR	IS	IT	IU	IV	IW
1	Revenue Category				Revenue Impacts	Revenue Impacts	Revenue Impacts	Revenue Impacts
2	10				3	3	3	3
3	250				254	255	256	257
4	Loss of Future Revenue:1:Financial Impact - Revenue	N/A:1:Financial Impact - Revenue	Loss of Future Revenue:2:Financial Impact - Revenue	N/A:2:Financial Impact - Revenue	1: <1 Hour:1:Financial Impact - Revenue	2: 4 Hours:1:Financial Impact - Revenue	3: 24 Hours:1:Financial Impact - Revenue	4: 72 Hours:1:Financial Impact - Revenue
5	1			N/A	0 - $0	0 - $0	1 - $1K-$5K	2 - $5K-$25K
6	1			N/A	0 - $0	0 - $0	1 - $1K-$5K	2 - $5K-$25K
7	1			N/A	0 - $0	0 - $0	1 - $1K-$5K	2 - $5K-$25K
8

If there could be a way to generate a lookup table that relates the headers in Process Information and use a duplicate sheet could these approaches be of any use?

**ExcelNoob243** · 06-19-2015, 12:12 AM

Sorry for the many replies, just trying to be clear:

Like when I just want to simply copy the information linearly column for column and don't need any modifications inside of the array (like repeating functions or splitting between the different business processes) I would use the following code:

With Sheets("InputsFinal")
        Sheets("DataFinal").Cells(1).CurrentRegion.Offset(1) _
            .Columns(.[c2].Value & ":" & .[d2].Value).Copy _
            Destination:=Sheets("SurveyMetaData").Range("B10")
    End With

**FlameRetired** · 06-19-2015, 12:54 AM

OK. I'm beginning to understand. I now see that you are looking for a VBA solution. I was thinking along the lines of spreadsheet functions INDEX / MATCH approaches.

Unfortunately I am not VBA proficient. I saw your post in Unanswered Threads. I thought I had seen it with spreadsheet function tags. I now see at the top of this page it is under VBA / macros.

My bad.

I am very sorry.

My apologies.

Dave (FlameRetired)

**ExcelNoob243** · 06-19-2015, 01:02 AM

Yup no problem! Yeah I am A LOT better at Index/Match, but VBA is needed for this (not my expertise).

Copy and Paste with a one to many relationship

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy and Paste with a one to many relationship

Re: Copy and Paste with a one to many relationship

Re: Copy and Paste with a one to many relationship

Re: Copy and Paste with a one to many relationship

Re: Copy and Paste with a one to many relationship

Re: Copy and Paste with a one to many relationship

Re: Copy and Paste with a one to many relationship

Re: Copy and Paste with a one to many relationship

Thread Information

Users Browsing this Thread

Similar Threads

Copy and Paste Input, then iterate, copy and paste results, and then perform next scenario

[SOLVED] Copy paste data from excel to word. Copy paste from different cells.

VBA to insert row, copy formula from above does not keep relative relationship

2 Macro's: only vertical copy/paste action and copy-paste 14 columns to the right.

Copy and Paste problem:macros to copy sheet form one file and paste

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions