I would use VBA
Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long
Dim NextRow As Long
Dim aryUsers As Variant
Dim NumUsers As Long
Dim sh As Worksheet
aryUsers = Array("Tom", "Joe")
Set sh = Worksheets("Sheet2")
With Worksheets("Sheet1")
.Rows(1).Copy sh.Range("A1")
sh.Columns("B").Insert
sh.Range("B1").Value = "Resource"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
NextRow = 1
For i = 2 To LastRow
For j = LBound(aryUsers) To UBound(aryUsers)
NextRow = NextRow + 1
.Cells(i, "A").Copy sh.Cells(NextRow, "A")
sh.Cells(NextRow, "B").Value = aryUsers(j)
.Cells(i, "B").Resize(, 6).Copy sh.Cells(NextRow, "C")
Next j
Next i
End With
End Sub
HTH
Bob
"primed" wrote in message
...
The X's in table 2 need to be automatically populated from the X's in
Table 1
using the project and phase columns as criteria.
Table1
Project Phase 1 2 3 4 5
1 Initiation x x x
1 Construction x x x
2 Initiation x x
2 Construction x x x x
Table 2
Project Phase Resource 1 2 3 4 5
1 Initiation Tom x x x
1 Initiation Joe x x x
2 Initiation Tom x x
2 Initiation Joe x x
1 Construction Tom x x x
1 Construction Joe x x x
2 Construction Tom x x x x
2 Construction Joe x x x x
"Per Jessen" wrote:
HI
I am confused now!
You say that you want to 'copy' the result from the matching row !?!
I have a feeling that a SUMPRODUCT formula can do what you need, but
to be sure, can you post some sample data, with an description of what
you want and the expected result.
Regards,
Per
On 22 Jan., 04:26, primed wrote:
Correct, which makes the countif solution not workable. ie i have to
search
two colums for two criteria matches then copy the result from the
matching
row in column (Table1[2]).
Table1[2] used to contain a number 1 it now contains an X or is blank.
Any more suggestions?
Your help much appreciated.
Regards
Primed
"Bob Phillips" wrote:
COUNTIFS doesn't require a sumrange.
HTH
Bob
"primed" wrote in message
...
Tried countif comes up with "too few arguments"
I looked at the syntax of countifs and there doesnt appear to be an
equivalent "sumrange", which in the formula is Table1[2].
Some additional info that you may require,
Table1[2] contains either a X or is blank and there will only ever
be 1
cell
in the range Table1[2] that is a match.
Any more ideas?
Regards
Primed
Hi
"Bob Phillips" wrote:
SUMIFS/COUNTIFS are Excel 2007 functions!
Bob
"joel" wrote in message
...
Use Countif instead of Sumif
from
=SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38)
to
=CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38)
Do you really have a function Countifs with an "S" at the end?
thie
maybe an UDF that needs to be modified.
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=171628
Microsoft Office Help
.
.- Skjul tekst i anførselstegn -
- Vis tekst i anførselstegn -
.