Insert rows and copying values if cells contain values

**Waninio** · 12-11-2011, 08:57 PM

Hey Guys,

I want to create a macro in Excel. An example of the data is added to this thread.

First thing you notice when you open the data is the top row with all the different variables.
Now as you notice this line of data is just one out of a bigger dataset. You also see that not every cell has got a value, this is normal. What I want to achieve is some kind of a transpose, I guess, but I have illustrated this in the dataset as well.

First Thing:
So what I need is when a cell contains a value, for instance cell N2, I need the variable name, in this case it's "g", to be copied to the first cell. Then I need the corresponding value, this is 2 in this case, and I also need the corresponding value next to this variable, this is cell O2 and corresponds to 5.
I call these variables "row specific" since every row will be unique, and the number of rows will be depended on how many cells contain a value. In this case only 2 cells contain a value (yes, 4 indeed but "g and b01g" belong together as do "j" and "b01j").

Second Thing:
There are also "fixed" variables. The values of these variables need to be copied as well to the different rows. An example is "ID", cell A2. Other examples are "Fixed" (cell AH2) and "Bpost" (cell AI2).

Anyways, hope someone can help me and let me know if more explanation is needed!

Sincerely

**rylo** · 12-11-2011, 09:13 PM

Hi

Add a new sheet to the workbook, called sheet2 then try

Please Login or Register  to view this content.

HTH

rylo

**Waninio** · 12-12-2011, 03:27 PM

Thanks rylo!

The test worked like a charm. However, I only copied a sample out of the dataset. In fact, I've got over 30 "fixed" variables that I need to copy. How do I add them in the script? I presume that "OutSh.Cells()" should do the trick, one for every variable. However, what values do I need to use then?

Thanks a million!

**rylo** · 12-12-2011, 05:57 PM

Hi

Well you could just continue with the structure of the

Please Login or Register  to view this content.

where ? is the column that gets the fixed variable and "??" is the column that contains it.

However, if you have a block of fixed variables that are in contiguous cells, then you may be able to transfer / copy a block of cells more easily. Can you give a more detailed example of your entire structure and what has to be copied.

rylo

**Waninio** · 12-12-2011, 06:10 PM

Hi rylo,

I've attached the full document. All values are in sheet "TEST". However the variable names have changed a bit. Everything, except the following, are "fixed" so to say:
maat01a & belang01a again belong together
and these go up to maat01p & belang01p (T1:AY1). In the previous example I gave them a simpler name.

The structure of the output is in the other sheet "Uitkomst". The fixed output variables are cells A1 and E1:AR1; the variable output variables are cells B1,C1,D1.

I hope you still get it but the previous macro worked fabulous!

Greetings

**rylo** · 12-12-2011, 06:34 PM

Hi

Can you update your example file with an expected output from the sample data. I can find most of the items, but don't have the foggiest where the data for B1, C1 and D1 is supposed to come from, or where the output from T to AY is supposed to go on the output sheet.

rylo

**Waninio** · 12-12-2011, 06:40 PM

I updated the file with the first ID, number 908.

**rylo** · 12-12-2011, 07:03 PM

Hi

Think I've matched them all.

Please Login or Register  to view this content.

If this doesn't match, then give the cell references of where the data in Uitkomst comes from in TEST.

rylo

**Waninio** · 12-12-2011, 07:20 PM

Yes indeed, I calculated the number of cells containing a value and it equals 1690. Great, thanks! This saved me a lot of time and moreover a smaller chance to make errors ;-)

Insert rows and copying values if cells contain values

LinkBack

Thread Tools

Rate This Thread

Display

Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Re: Insert rows and copying values if cells contain values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions