Matching then Aligning Data across rows and down columns

**JBeaucaire** · 12-23-2008, 12:12 PM

I should've added this before, a check cell to watch if any revenue "goes missing" during the conversion. If I'd had that before we would've spotted the first problem sooner, and this new one with multiple entries in the TAN column would've shown missing revenue, too.

Anyway, it's added now in the AH column. If that turns red, ever, then something's gone missing and it will show how much $ is unaccounted for in the new chart. Right now it keeps showing ZERO because I changed the formulas again. Now they use a SUMIF() to spot multiple identical entries in the same column and add them up.

**bsacco** · 12-23-2008, 01:46 PM

Well, I have a 2 part answer.

PART 1 - Duplicates in the TAN data

Yes, duplicates occur. This is the way the data comes to me out of Salesforce.com (our reporting tool).

What is happening here is that a vendor may have several DIFFERENT ad campaigns running at any given time, it's just that the tool reports the ADVERTISER"S COMPANY name NOT the specific name of the ad campaign,

Example: There may be two ad campaigns , "American Airlines Vacations_2009" and "American Airlines AA.com Transactional campaign Bwiz09" BUT they are reported as duplicated line items as "American Airlines - HQ - TX" under the vendor column.

A solution I found on the web was to possibly do a Group And Sum Data formula on this raw data BEFORE running it into your MACRO?

The formula I found is below. Of course it would have to be re-named to work with our data.

=IF(ROW(A1)>SUM(1/COUNTIF(Staff,Staff&"")),"",INDEX(Staff,MATCH(LARGE(IF(MATCH(Staff&"",Staff&"",0)=ROW(INDIRECT("1:"& ROWS(Staff))),SUMIF(Staff,Staff,Amount)+1/ROW(Staff),0),ROW(A1)),SUMIF(Staff,Staff,Amount)+1/ROW(Staff),0)))

In regards to how the data comes to me for the other columns, the DATA ARRIVES UNDUPLICATED.

So, we are only dealing with DUPLICATION in regards to the TAN data.

PART 2 - "editing vendors for TAN to match the other columns"

You say,

"Also, right now, there is no correlation between the TAN revenue and the other sites. There are no common names so the TAN vendors are ending up in the master list under rows of their own, therefore all TAN revenue is 100% of the total for that row.

You going to edit these vendors for TAN to match the other columns more specific entries?"

Yes, I think I'll have to EDIT those values that I think match up. My concern is that for the values the DO NOT MATCH will they break up our report with extra rows because TAN Revenue clients will CREATE a NEW ROW to place the data?

Is there a way to ensure that the new ROWS created will be inserted at the BOTTOM OF THE REPORT?

-bob

**Simon Lloyd** · 12-27-2008, 03:06 PM

Bob please read the forum rules
Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Crossposted here http://www.thecodecage.com/forumz/ex...tml#post160167

**bsacco** · 12-27-2008, 05:46 PM

I'm a newbie to this all...didn't mean to break the rules...I kinda forgot where I was...

**royUK** · 12-28-2008, 03:55 AM

I would suggest that the code could be much quicker without selecting the ranges.

**JBeaucaire** · 12-28-2008, 10:13 AM

This version does much less "selecting" than that earlier version. And separates the results CHART onto another sheet.

Would love to be able to figure out how to make the array thing they tried to get working over on CodeCage actually work, but if there are any gaps in the data sets it leaves data out, so the array needs to use set values. I figured 2000 rows would be sufficient, but couldn't figure it out after playing with the array for a quite awhile.

Version 12 is the shortest of all so far.

**JBeaucaire** · 12-28-2008, 07:14 PM

Final Version (14) posted on CodeCage where OP last posted for help.

http://www.thecodecage.com/forumz/ex...tml#post160780

**Anwaar** · 08-12-2010, 02:44 PM

Hi Jerry,

Need your help. I see you have resolved something similar to my need.

Example:

...........Column A.............Column B...........Column C...........Column D
ROW 1: 123456 ..............7/26/10..................................................
ROW 2: .................................................123456 ............8/6/2010..

Desired result:

...........Column A.............Column B...........Column C...........Column D
ROW 1: 123456 ..............7/26/10..............123456 ............8/6/2010

Any help would be appreciated.

Thanks
Anwaar