Do Not Know What Forumla to Use for This Task

[1313]

Hi,

I am having trouble deciding what formula to use in Excel for task I have below. In the original spread sheets there are approximately 580 individuals records that need to be worked on. Therefore, I do not want to do this manually.

I need to get data from spread sheet A to populate into spread sheet B.
There are multiple records (each = a single row) on spread sheet A, and only a single record (one row) in spread sheet B.
Column Headers in spread sheet B, is the data that is listed in spread sheet A (Column B).

Will also need to do similar data exchange using the same column headers and data, but switching fiscal year for dollar amount.

Spread Sheet A

	A	B	C
1	ConstituentID	Campaign	Fiscal Year
2	1004200	Current-Unrestricted	2008
3	1004200	Capital	2009
4	1004200	Endowment	2010
5	1007850	Current-Unrestricted	2012
6	1007850	Capital	2012

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Spread Sheet B

	A	B	C	D
1	Cons ID	Current-Unrestricted	Capital	Endowment
2	1004200
3	1007850

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

What I need spread sheet B to look like, completed

	A	B	C	D
1	Cons ID	Current-Unrestricted	Capital	Endowment
2	1004200	2008	2009	2010
3	1007850	2012	2012

<tbody>
</tbody>

 

[par60056]

Is the combination of columns A&B unique? or can for example 1007850 have endowment records for 2010 and 2011?

 

[1313]

Is the combination of columns A&B unique? or can for example 1007850 have endowment records for 2010 and 2011?

Yes, 1007850 can have endowment records for both 2010 and 2011. They can also have Endowment records (for different $$ amounts), for the same Fiscal Year. Those, however are few and I will handle those manually.

 

[par60056]

too bad. I was thinking that it would be simple to make a pivot table with the IDs for rows and the campaign for columns and the year as the data.

Does it need to be dynamic or can you use a macro to build sheet B from sheet A?

 

[1313]

too bad. I was thinking that it would be simple to make a pivot table with the IDs for rows and the campaign for columns and the year as the data.

Does it need to be dynamic or can you use a macro to build sheet B from sheet A?

I thought about a macro, but there are about 45 other columns on sheet B that are not on sheet A, that need to be there. I've been working on this since last week, trying to come up with a formula... this one has me stumped. Im not an Excel expert, by any means... but I can figure this one out.

Thanks for your time with it.

 

[par60056]

So sheet B already exists with all the columns and headings?

I am assuming that both SheetA and SheetB start in A1.

You will need to go into the macro editor and go to the Tools->References menu. This brings up a box that says "Available References". Find the item "Microsoft Scripting Runtime" and check it. This macro makes us of the dictionary object which is part of that.

The macro reads through sheetA and builds a dictionary of values by joining the first 2 columns into a key. Then it goes through SheetB looking for the values in the dictionary and if it finds something it puts it in the cell.

Sub fillB()
Dim CampaignCol As Integer
Dim sourceSheet As String
Dim targetSheet As String
Dim dataDict As New Dictionary
Dim key As String
Dim lastRow As Long
Dim currRow As Long
Dim currCol As Integer
Dim theYear As String


sourceSheet = "Sheet A"
targetSheet = "Sheet B"


lastRow = Sheets(sourceSheet).UsedRange.Rows.Count


For currRow = 2 To lastRow
    key = Sheets(sourceSheet).Cells(currRow, 1) & "|" & Sheets(sourceSheet).Cells(currRow, 2)
    theYear = Sheets(sourceSheet).Cells(currRow, 3)
    If dataDict.Exists(key) Then
        dataDict(key) = dataDict(key) & ", " & theYear
    Else
        dataDict.Add key, theYear
    End If
Next


lastRow = Sheets(targetSheet).UsedRange.Rows.Count
CampaignCol = 2


For currRow = 2 To lastRow
    For currCol = CampaignCol To CampaignCol + 2
        key = Sheets(sourceSheet).Cells(currRow, 1) & "|" & Sheets(sourceSheet).Cells(1, currCol)
        If dataDict.Exists(key) Then
            Cells(currRow, currCol) = dataDict(key)
        End If
    Next
Next


End Sub

 

[zeedorio]

this is my first post and i m not sure if i am posting in correct forum, actually I am to select the active cell in an excel sheet and another cell, lets say forth cell from the active cell and not the cells between the two cells, but when I use the following code</SPAN>

Range(Activecell, Activecell.Offset(0, 4)).select</SPAN>

It selects all the cells from activecell to the forth cell</SPAN>
Can anyone throw some light on this</SPAN>

 

[Marcelo Branco]

Hi,

I am having trouble deciding what formula to use in Excel for task I have below. In the original spread sheets there are approximately 580 individuals records that need to be worked on. Therefore, I do not want to do this manually.

I need to get data from spread sheet A to populate into spread sheet B.
There are multiple records (each = a single row) on spread sheet A, and only a single record (one row) in spread sheet B.
Column Headers in spread sheet B, is the data that is listed in spread sheet A (Column B).

Will also need to do similar data exchange using the same column headers and data, but switching fiscal year for dollar amount.

Spread Sheet A

	A	B	C
1	ConstituentID	Campaign	Fiscal Year
2	1004200	Current-Unrestricted	2008
3	1004200	Capital	2009
4	1004200	Endowment	2010
5	1007850	Current-Unrestricted	2012
6	1007850	Capital	2012

<tbody>
</tbody>

Spread Sheet B

	A	B	C	D
1	Cons ID	Current-Unrestricted	Capital	Endowment
2	1004200
3	1007850

<tbody>
</tbody>

What I need spread sheet B to look like, completed

	A	B	C	D
1	Cons ID	Current-Unrestricted	Capital	Endowment
2	1004200	2008	2009	2010
3	1007850	2012	2012

<tbody>
</tbody>

Maybe this array formula (Excel 2007 or higher)

Sheet B

A B C D (headers in row 1)

Cons ID	Current-Unrestricted	Capital	Endowment
1004200	2008	2009	2010
1007850	2012	2012

<tbody>
</tbody>

B2
=IFERROR(INDEX(A!$C$2:$C$100,MATCH(1,IF(A!$A$2:$A$100=$A2,IF(A!$B$2:$B$100=B$1,1)),0)),"")

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

copy acrosss and down

M.

 

[par60056]

Marcelo, That is awesome.

Now I need to figure out WHY it works so I can use it if I need to.

The only problem is that it won't deal with the special case where there is more than a single value for the row/column intersection. The original poster said that was rare and they would deal with the special cases. I hate to leave anything to hand fixing but if a 98% solution is good enough (which it often is), then this is it.

I have a lot to learn about how the array formulas work.

 

[Marcelo Branco]

Marcelo, That is awesome.

Now I need to figure out WHY it works so I can use it if I need to.

The only problem is that it won't deal with the special case where there is more than a single value for the row/column intersection. The original poster said that was rare and they would deal with the special cases. I hate to leave anything to hand fixing but if a 98% solution is good enough (which it often is), then this is it.

I have a lot to learn about how the array formulas work.

If the data were like below maybe a macro would be a better solution.

A B C D

ConstituentID	Campaign	Fiscal Year
1004200	Current-Unrestricted	2008
1004200	Capital	2009
1004200	Endowment	2010
1004200	Current-Unrestricted	2011
1004200	Capital	2012
1004200	Endowment	2013
1007850	Current-Unrestricted	2011
1007850	Endowment	2011
1007850	Current-Unrestricted	2012
1007850	Capital	2012
1007850	Endowment	2012

<tbody>
</tbody>

But, just for fun, still would be possible to use formulas repeating the headers as many times as necessary (not very elegant... ) and using this array formula.

Sheet B

A B C.................

Cons ID	Current-Unrestricted	Capital	Endowment	Current-Unrestricted	Capital	Endowment
1004200	2008	2009	2010	2011	2012	2013
1007850	2011	2012	2011	2012		2012

<tbody>
</tbody>

=IFERROR(INDEX(A!$C:$C,SMALL(IF(A!$A$2:$A$100=$A2,IF(A!$B$2:$B$100=B$1,ROW(A!$C$2:$C$100))),COUNTIF($B$1:B$1,B$1))),"")

confirmed with Ctrl+Shift+Enter

copy across and down

M.