Copy cell contents to new cells automaticly

[enne]

Hello,
I have no experience with Excel, but I have to use it

I use a spreadsheet with 100 rows. Looks like this:

AAA | W | X | Y | Z
----------------------------
ABC | 5 | 8 | 11 | 4
DEF | 0 | 2 | 4 | 10
GHI | 3 | 68 | 7 | 34

My input (can be copied into Excel) looks like this:

-----------------------------
ABC | W | 5
ABC | X | 8
ABC | Y | 11
ABC | Z | 4
DEF | W | 0
DEF | X | 2
DEF | Y | 4
DEF | Z | 10
GHI | W | 3
GHI | X | 68
GHI | Y | 7
GHI | Z | 34

The spreadsheet is growing and now I want to save time and look for a possiblity to make the updates easier.

The macro or whatever shoud look to the input and do the following: ABC, value for W is 5, and update the correct cell on my spreadsheet and so on, until the last row of the input is reached.

Can someone give me some instruction how to do?
This message was edited by enne on 2002-05-06 06:52
This message was edited by enne on 2002-05-06 06:54
This message was edited by enne on 2002-05-06 06:57

 

[Brian]

USING EQUATIONS:

STEP 1:
Place your source text anywhere in the same workbook, as you showed.

STEP2:
Select the upper left corner cell of data (ABC in your example, i think) in your source table. Then select Insert Menu > Name > Define > Enter ULC (as the name) > Click ADD {This has just named that cell so that you can refer to it in your equation).

STEP 3:
Create the following new table {A B C and 1 2 3 4 5 6 7 are to represent the column and row headings, not things to enter.}

A B C D
1 ROW COL NAME DATA
2 0 1 =offset(ULC,$A2,0) =offset(ULC,$A2,$B2)
3 0 2
4 0 3
5 0 4
6 =$A2+1 =$B2 =offset(ULC,$A6,0) =offset(ULC,$A6,$B6)

The function "=offset(ULC, 1,2)" is saying to excel "Go the cell named ULC; Then go down 1 row, and right 2 columns; and tell me what number is in that cell." See MS excel help.

STEP 3: Drag down the formulas (click the cell with the formula, then hover the mouse over the black square in the lower left corner until the cursor becomes a black cross. Then click and drag down the formular as far as you need. {Or select the entire range you want, then use Menu Edit > Fill > Down}

STEP 4: Check the equations For example, row # 7 will be:

7 =$A3+1 =$B3 =offset(ULC,$A7,0) =offset(ULC,$A7,$B7)

Good Luck,

Brian

 

[Brian]

Let's try that again, using _ to space out the text:

_____A__________B___________C___________D
1____ROW_______COL________NAME________DATA
2_____0_________1_____=offset(ULC,$A2,0)___=offset(ULC,$A2,$B2)_
3_____0_________2_
4_____0_________3
5_____0_________4
6____=$A2+1___=$B2_____=offset(ULC,$A6,0)___=offset(ULC,$A6,$B6)_


Col A ___ col B should look like:
0__1
0__2
0__3
0__4
1__1
1__2
1__3
1__4
2__1
2__2
2__3
2__4
3__1

This assumes that you have 4 columns of data. Adjust accordingly.

 

[enne]

Hi Brian,
thanks for your reply. I will try it.

Stephan

 

[enne]

Hi Brian,

sorry, I forgot to say, that the input is ordered alphabetic in the first column and numeric in the second column.

But the result in my spreadsheet is a unordered list (the list has a order, but not the same as the input and can't be changed.)

The list should look like this:

AAA | W | X | Y | Z
----------------------------
GHI | 3 | 68 | 7 | 34
DEF | 0 | 2 | 4 | 10
ABC | 5 | 8 | 11 | 4

Therefore I need equations, or something different performing a function like this:
goto worksheet input, search for GHI in column 1 and for W in column 2 and return the value of column 3 in this row.

Is this possible?

Thanks, Stephan

 

[Colo]

Hi enne. If your spreadsheet is like this,please copy this into a standard module and run "Test".<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>Microsoft Excel - Copy cell contents to new cells automaticly.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>A1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8>=</TD><TD COLSPAN=3 BGCOLOR=White>AAA</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=White>AAA</TD><TD BGCOLOR=White> W</TD><TD BGCOLOR=White> X</TD><TD BGCOLOR=White> Y</TD><TD BGCOLOR=White> Z</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=White>GHI</TD><TD BGCOLOR=White>3</TD><TD BGCOLOR=White>68</TD><TD BGCOLOR=White>7</TD><TD BGCOLOR=White>34</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=White>DEF</TD><TD BGCOLOR=White>0</TD><TD BGCOLOR=White>2</TD><TD BGCOLOR=White>4</TD><TD BGCOLOR=White>10</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=White>ABC</TD><TD BGCOLOR=White>5</TD><TD BGCOLOR=White>8</TD><TD BGCOLOR=White>11</TD><TD BGCOLOR=White>4</TD></TR><TR><TD COLSPAN=6><U>input</U></TD></TR></TABLE></CENTER><PRE>
Sub Test()
Dim rng As Range, lngRow As Long, intCol As Integer
With Application
.ScreenUpdating = False
.DisplayAlerts = False
On Error Resume Next
Sheets("4Test").Delete
On Error GoTo 0
Sheets.Add.Name = "4Test"
With Sheets("input")
For Each rng In Range(.[A2], .[A65536].End(xlUp))
For intCol = 1 To 4
lngRow = lngRow + 1
rng.Copy Sheets("4Test").Cells(lngRow, 1)
Union(rng.Offset(, intCol), .[A1].Offset(, intCol)).Copy
Sheets("4Test").Cells(lngRow, 2).PasteSpecial , Transpose:=True
Next
Next
End With
.CutCopyMode = False
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub</PRE>

_________________
<FONT face="Century Gothic" size=4 color=#339966>With regards,</FONT>
<FONT face="Century Gothic" size=4 color=#000080>C</FONT><FONT face="Century Gothic" size=4 color=#0000FF>o</FONT><FONT face="Century Gothic" size=4 color=#3366FF>l</FONT><FONT face="Century Gothic" size=4 color=#00CCFF>o</FONT> 

<MARQUEE scrollamount=8 behavior=alternate>

</MARQUEE>
This message was edited by Colo on 2002-05-07 23:03

 

[enne]

Hello Colo,

thanks for your answer. Unfortunately I don't get the result. I try again to explain and be more specific with the input and output.

My worksheet called 'overview' looks like this:
__A______B___C_____D______E______F______G
1_
2_Name___________Data1__Data2__Data3__Data4
3_
4_Bill
5_Hugo
6_John

The data to be filled in is from D4 to G6.

My input worksheed called 'input' looks like this:

___A______B______C______
1__Name__Check__Problem
2__Hugo__Data1___5
3__Hugo__Data2___8
4__Hugo__Data3___11
5__Hugo__Data4___4
6__John__Data1___0
7__John__Data2___4
8__John__Data3___2
9__John__Data4___10
10_Bill__Data1___3
11_Bill__Data2___68
12_Bill__Data3___7
13_Bill__Data4___34

The data in the 'overview' worksheet is around 100 rows and growing. New Names will be added at the end of the list. The input has a different sort order, therefore I need the following:

Cell D4 on the 'overview' sheet needs the value from cell C10 out of the 'input' sheet.

That means, the macro or equations for cell D4 should look for the value from the problem column for Bill and Data1.

Row 2 and column 2 and 3 are used for other data in the 'overview' sheet.

Thank you very much.
This message was edited by enne on 2002-05-09 02:09

 

[Aladin Akyurek]

I try again to explain and be more specific with the input and output.

My worksheet called 'overview' looks like this:
__A______B___C_____D______E______F______G
1_
2_Name___________Data1__Data2__Data3__Data4
3_
4_Bill
5_Hugo
6_John

The data to be filled in is from D4 to G6.

My input worksheed called 'input' looks like this:

___A______B______C______
1__Name__Check__Problem
2__Hugo__Data1___5
3__Hugo__Data2___8
4__Hugo__Data3___11
5__Hugo__Data4___4
6__John__Data1___0
7__John__Data2___4
8__John__Data3___2
9__John__Data4___10
10_Bill__Data1___3
11_Bill__Data2___68
12_Bill__Data3___7
13_Bill__Data4___34

The data in the 'overview' worksheet is around 100 rows and growing. New Names will be added at the end of the list. The input has a different sort order, therefore I need the following:

Cell D4 on the 'overview' sheet needs the value from cell C10 out of the 'input' sheet.

That means, the macro or equations for cell D4 should look for the value from the problem column for Bill and Data1.

Row 2 and column 2 and 3 are used for other data in the 'overview' sheet.

I'll assume that A1:C14 in sheet Input houses the sample you provided:

{"Name","Check","Problem ";
"Hugo","Data1",5;
"Hugo","Data2",8;
"Hugo","Data3",11;
"Hugo","Data4",4;
"John","Data1",0;
"John","Data2",4;
"John","Data3",2;
"John","Data4",10;
"Bill","Data1",3;
"Bill","Data2",68;
"Bill","Data3",7;
"Bill","Data4",34}

and the sample is sorted on column A.

Activate Insert|Name|Define.
Enter Nrecs as name in the Names in workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,Input!$C:$C)

Activate Add. (Don't leave yet the Define Name window.)

Enter Drecs as name in the Names in workbook box.
Enter as formula in the Refers to box:

=Nrecs-(ROW(Input!$A$2)-1)

Activate Add. (Don't leave yet the Define Name window.)

Enter NAMES as name in the Names in workbook box.
Enter as formula in the Refers to box:

=OFFSET(Input!$A$2,0,0,Drecs,1)

Activate Add. (Don't leave yet the Define Name window.)

Enter CHECKS as name in the Names in workbook box.
Enter as formula in the Refers to box:

=OFFSET(Input!$B$2,0,0,Drecs,1)

Activate OK.

I'll assume that A2:E5 houses the following in sheet Overview:

{"Name","Data1","Data2","Data3","Data4";
"Bill","","","","";
"Hugo","","","","";
"John","","","",""}

where "" stands for an empty cell.

In B1:E1 enter: {1,2,3,4}

In B3 enter:

=IF(LEN($A3),INDEX(CHECKS,MATCH($A3,NAMES,0)),"")

In C3 enter:

=IF(AND(LEN($A3),COUNTIF(NAMES,$A3)>=C$1),INDEX(CHECKS,MATCH($A3,NAMES,0)+C$1-1),"")

Select B3:C3 and copy across first to E3 then down as far as needed.

That's what you get to see in the results area in Overview:

{"",1,2,3,4;"Name","Data1","Data2","Data3","Data4";
"Bill","Data1","Data2","Data3","Data4";
"Hugo","Data1","Data2","Data3","Data4";
"John","Data1","Data2","Data3","Data4"}

If in trouble implementing the foregoing, ask for the example workbook that shows all this.

Aladin

 

[enne]

Hi Aladin,

thanks a lot, great job. It works perfect after a few changes. I had to replace the , in the formulas to a ; and changed the formula for the CHECKS to
=OFFSET(Input!$B$2;0;1;Drecs;1).

Now is only a small problem left.

If the input has for example for John only 2 rows of data (Data3 and Data4), than the value of Data3 is put into the Data1 field and the value for Data4 into Data2 field. Data3 and Data4 is blank.

Is there a chance to get this adjusted.
If not, than I have only a little bit manual work. Better than before.

Again, many thanks!!!
Stephan

 

[Aladin Akyurek]

I had to replace the , in the formulas to a ; and changed the formula for the CHECKS to
=OFFSET(Input!$B$2;0;1;Drecs;1).

Yes, the list separator "," must be changed to ";" in European language versions of Excel.

Now is only a small problem left.

If the input has for example for John only 2 rows of data (Data3 and Data4), than the value of Data3 is put into the Data1 field and the value for Data4 into Data2 field. Data3 and Data4 is blank.

Is there a chance to get this adjusted.
If not, than I have only a little bit manual work. Better than before.


I took those Data1,... to be just data like numbers or names. But, it seems to be things whose numbering has something significant?

Care to elaborate what that is? I'm asking this because the formulas as they are now treat them just ordinary data. If Data1 has to be recognized as Data1, the formulas need to be modified. So what are they?

Aladin