Do Not Know What Forumla to Use for This Task

1

1313

New Member
Joined
Dec 8, 2011
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

[TABLE="class: grid, width: 450"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ConstituentID
[/TD]
[TD]Campaign[/TD]
[TD]Fiscal Year[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1004200[/TD]
[TD]Current-Unrestricted
[/TD]
[TD]2008
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1004200[/TD]
[TD]Capital
[/TD]
[TD]2009[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1004200[/TD]
[TD]Endowment
[/TD]
[TD]2010[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1007850
[/TD]
[TD]Current-Unrestricted
[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1007850
[/TD]
[TD]Capital
[/TD]
[TD]2012
[/TD]
[/TR]
</tbody>[/TABLE]


Spread Sheet B

[TABLE="class: grid, width: 450"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Cons ID
[/TD]
[TD]Current-Unrestricted[/TD]
[TD]Capital[/TD]
[TD]Endowment[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1004200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1007850[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I need spread sheet B to look like, completed

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Cons ID
[/TD]
[TD]Current-Unrestricted[/TD]
[TD]Capital[/TD]
[TD]Endowment[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1004200[/TD]
[TD]2008
[/TD]
[TD]2009
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1007850[/TD]
[TD]2012
[/TD]
[TD]2012
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Is the combination of columns A&B unique? or can for example 1007850 have endowment records for 2010 and 2011?
 
Upvote 0
par60056 said:
Is the combination of columns A&B unique? or can for example 1007850 have endowment records for 2010 and 2011?
Click to expand...

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.
 
Upvote 0
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?
 
Upvote 0
par60056 said:
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?
Click to expand...

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.
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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>
 
Upvote 0
1313 said:
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

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ConstituentID

[/TD]
[TD]Campaign
[/TD]
[TD]Fiscal Year
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1004200
[/TD]
[TD]Current-Unrestricted
[/TD]
[TD]2008
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1004200
[/TD]
[TD]Capital
[/TD]
[TD]2009
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1004200
[/TD]
[TD]Endowment
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1007850
[/TD]
[TD]Current-Unrestricted
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1007850
[/TD]
[TD]Capital
[/TD]
[TD]2012
[/TD]
[/TR]
</tbody>[/TABLE]


Spread Sheet B

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Cons ID

[/TD]
[TD]Current-Unrestricted
[/TD]
[TD]Capital
[/TD]
[TD]Endowment
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1004200
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1007850
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I need spread sheet B to look like, completed

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Cons ID

[/TD]
[TD]Current-Unrestricted
[/TD]
[TD]Capital
[/TD]
[TD]Endowment
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1004200
[/TD]
[TD]2008
[/TD]
[TD]2009
[/TD]
[TD]2010
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1007850
[/TD]
[TD]2012
[/TD]
[TD]2012
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Maybe this array formula (Excel 2007 or higher)

Sheet B

A B C D (headers in row 1)
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Cons ID
[/TD]
[TD="width: 138, bgcolor: transparent"]Current-Unrestricted
[/TD]
[TD="width: 64, bgcolor: transparent"]Capital
[/TD]
[TD="width: 82, bgcolor: transparent"]Endowment
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent, align: right"]2008
[/TD]
[TD="bgcolor: transparent, align: right"]2009
[/TD]
[TD="bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007850
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
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.
 
Upvote 0
par60056 said:
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.
Click to expand...

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

A B C D
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 94, bgcolor: transparent"]ConstituentID
[/TD]
[TD="width: 138, bgcolor: transparent"]Campaign
[/TD]
[TD="width: 71, bgcolor: transparent"]Fiscal Year
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent"]Current-Unrestricted
[/TD]
[TD="bgcolor: transparent, align: right"]2008
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent"]Capital
[/TD]
[TD="bgcolor: transparent, align: right"]2009
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent"]Endowment
[/TD]
[TD="bgcolor: transparent, align: right"]2010
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent"]Current-Unrestricted
[/TD]
[TD="bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent"]Capital
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent"]Endowment
[/TD]
[TD="bgcolor: transparent, align: right"]2013
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007850
[/TD]
[TD="bgcolor: transparent"]Current-Unrestricted
[/TD]
[TD="bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007850
[/TD]
[TD="bgcolor: transparent"]Endowment
[/TD]
[TD="bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007850
[/TD]
[TD="bgcolor: transparent"]Current-Unrestricted
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007850
[/TD]
[TD="bgcolor: transparent"]Capital
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007850
[/TD]
[TD="bgcolor: transparent"]Endowment
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
</tbody>[/TABLE]



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.................
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Cons ID
[/TD]
[TD="width: 138, bgcolor: transparent"]Current-Unrestricted
[/TD]
[TD="width: 64, bgcolor: transparent"]Capital
[/TD]
[TD="width: 82, bgcolor: transparent"]Endowment
[/TD]
[TD="width: 138, bgcolor: transparent"]Current-Unrestricted
[/TD]
[TD="width: 64, bgcolor: transparent"]Capital
[/TD]
[TD="width: 82, bgcolor: transparent"]Endowment
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1004200
[/TD]
[TD="bgcolor: transparent, align: right"]2008
[/TD]
[TD="bgcolor: transparent, align: right"]2009
[/TD]
[TD="bgcolor: transparent, align: right"]2010
[/TD]
[TD="bgcolor: transparent, align: right"]2011
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[TD="bgcolor: transparent, align: right"]2013
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1007850
[/TD]
[TD="bgcolor: transparent, align: right"]2011
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[TD="bgcolor: transparent, align: right"]2011
[/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
</tbody>[/TABLE]


=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
246
MeaclH
M
S
  • Question Question
Help on complicated VBA code
Replies
3
Views
749
superfb
S
X
  • Question Question
Need help in copy and pasting multiple lines based on a criteria
Replies
0
Views
596
xlbaby
X
B
  • Question Question
How do I add additional input fields to the right of the Order Id, Supposedly it has a macros that needs to be edited, but I cant find.
Replies
2
Views
830
Fluff
Fluff
B
  • Question Question
Need help creating a second ListBoxOutput without corrupting the first.
Replies
3
Views
421
Blasirl
B

Forum statistics

Threads
1,223,757
Messages
6,174,327
Members
452,555
Latest member
colc007

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top