# Do Not Know What Forumla to Use for This Task

#### 1313

##### New Member
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.

 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>

 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>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Is the combination of columns A&B unique? or can for example 1007850 have endowment records for 2010 and 2011?

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.

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?

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.

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.

Code:
``````Sub fillB()
Dim CampaignCol As Integer
Dim sourceSheet As String
Dim targetSheet As String
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)
Else
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)
End If
Next
Next

End Sub``````

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>

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.

 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>

 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.

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, 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.

Replies
3
Views
503
Replies
0
Views
435
Replies
2
Views
551
Replies
3
Views
201
Replies
4
Views
8K

1,206,826
Messages
6,075,092
Members
446,119
Latest member
BrianAndrews

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back