• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy ID between sheets then add rows based on contract length

cbecker

cbecker

New Member
The goal is to copy A3 within the MediaBuy sheet to A3 on the Data-Usage sheet but then also add rows equivalent to (or greater than) Column F MediaBuy (Months in Contract) value.

So for example, MonsterJob Posting4164042004 (MediaBuy A3) would have at least 12 rows added to the Data-Usage sheet.

Ideally, I even have the buffer to add more than the contract length in rows.

Most importantly, I do not want to use Macros or even VBA where ever possible.

thanks
chris
 

Attachments

By A3, I assume you also mean the corresponding 3 columns, along with the 12 months. And in place of 12, it could actually be any number, based on your choosing, and also, it means that each contract will repeated for the same length. In your case for example, it was always 12. Are these assumptions correct?
 
I only need the A column to populate with the ID from the MediaBuy sheet but I need at least the number of rows that equates to the contract length in months for that particular ID. If the contract start date is 3/1/14 to 6/30/14 i need at least 4 rows for that particular ID starting with 3/1/14.

Does that clarify?
 
Use this in Data-Usage!A3

=IFERROR(INDEX(MediaBuy!$A$3:$A$698,IF(COUNTIF(A$2:A2,A2)=INDEX(MediaBuy!$F$3:$F$698,MATCH(A2,MediaBuy!$A$3:$A$698,0)),MATCH(A2,MediaBuy!$A$3:$A$698,0)+1,MATCH(A2,MediaBuy!$A$3:$A$698,0))),MediaBuy!$A$3)

And Data-Usage!B3

=IF(A3<>"",EOMONTH(INDEX(MediaBuy!$D$3:$D$698,MATCH('Data-Usage'!A3,MediaBuy!$A$3:$A$698,0)),COUNTIF(A$3:A3,A3)-2)+1,"")

and drag down. I'm sure you can replicate this for the other columns.
 

Attachments

I tried and unfortunately, all I got was the first ID copied all the way to the bottom, negating the three other IDs that need to be incorporated. What I expected to see was:
MonsterJob Posting4164042004 - with 12 rows
GlassdoorBanner4164041729 - with 3 rows
GlassdoorEmail4164041670 - with 1 row
CareerBuilderJob Posting4169941820 - with 4 rows

now to even further complicate things, I would like to have three extra rows for each of the four IDs above, so:
MonsterJob Posting4164042004 - with 15 rows
GlassdoorBanner4164041729 - with 6 rows
GlassdoorEmail4164041670 - with 4 rows
CareerBuilderJob Posting4169941820 - with 7 rows

does that clarify? I really need to cap the number of rows per ID to a minimum of the value in MediaBuy column F value and a maximum of +3 more rows.

thanks
 
I beg to differ. My current screen shows there was only one download of the file. And that was me! My attachment clearly answers your question. Except for the additional requirement you posted later.
 
And to take care of that additional requirement, it's just an addition of the value

=IFERROR(INDEX(MediaBuy!$A$3:$A$698,IF(COUNTIF(A$2:A2,A2)=3+INDEX(MediaBuy!$F$3:$F$698,MATCH(A2,MediaBuy!$A$3:$A$698,0)),MATCH(A2,MediaBuy!$A$3:$A$698,0)+1,MATCH(A2,MediaBuy!$A$3:$A$698,0))),MediaBuy!$A$3)
 
Sam,
I'm sorry, I might have made this confusing from the start. I had already placed the IDs in the A column (Data-usage) but I envision the A column in Data-Usage sheet populating when someone adds a row to the MediaBuy sheet. So, as more Elements are added within MediaBuy sheet the A column in Data-usage sheet would populate with the particular ID for however many rows (+3) for the contract length.

In my workbook, I had already entered the ID but if it can be built under the assumption that the A column in Data-Usage is empty until a new element is added, that would be ideal.

I tried the second equation and when I copied it down, the only ID I got was the first one and none of the other three.

sorry for the unnecessary confusion.
 
I am not sure how my solution is different from what you are saying. The irony is that I've done exactly as you've indicated. Can you once again, please, download the file, and tell me what is not matching with what you are saying. Here's the attachment please (it's the same as above, except that I've added a +3.
 

Attachments

That file is it exactly. I just couldn't get the formula you listed out to work when I pasted it to my original document. Thank you for your assistance on this. Again, I am amazed at the forums ability to help.
 
Sam,
This worked great. I cant thank you enough. I have however created another problem
{=SUM((Inv_Combo=$D44)*(Inv_Date>=F9)*(Inv_Date<=$L$4)*(Inv_Clicks))}

{=SUM((Inv_Combo=$D44)*(Inv_Date>=F9)*(Inv_Date<=$L$4)*(Inv_Cost))}

Inv_Combo is General format ID
Inv_Date is 1/1/14 Date format
Inv_Clicks is Number Format (raw data)
Inv_Cost is Currency Format (formula calculated data)

The first array formula worked and returned what I needed :where ID matches specific cell and date is greater than this date and less that this date then sum clicks = result as expected

The second array formula resulted in a #VALUE! error: where ID matches specific cell and date is greater than this date and less than this date then sum costs.

oddly enough when I changed the named range from Cost to Clicks the formula works.

can you please help with this one?
 
Hi, cbecker!
Using your sample file the $D44 and F9 cell references are in the middle of the worksheet area where the other used names are defined (row 3 thru 50). Despite of this, both formulas worked fine, well at least they displayed zero but not an error value.
So elaborate a bit more or upload an updated file with proper test values.
Regards!
 
Hi, cbecker!

In your original uploaded file the cells of worksheet Data-Usage H & J columns had these format:
H: _(* #.##0_);_(* (#.##0);_(* "-"_);_(@_)
J: $#.##0_);($#.##0)
In you last uploaded file the cells of same worksheet:
H: _(* #.##0_);_(* (#.##0);_(* "-"_);_(@_)
J: numeric with 2 decimal places

In both cases H column cells had numeric values, but in the original J column cells had numeric values too but in the last there's a formula at J:
J: =SI(ESERROR(L3*P3); "-"; (L3*P3)) -----> in english: =IF(ISERROR(L3*P3), "-", (L3*P3))

So how do you explain the error at J44? Well, you're assigning a string "-" in case of error instead of a zero and a proper format if you want to display a dash or minus sing and even SUM function handles this cases as zero the operation product doesn't.

Change J formula replacing the "-" by 0, format properly column J and you'll get it working.

Regards!
 
You must log in or register to reply here.
Back
Top