Hi All,
I have what I believe to be a simple problem, but for the life of me, i can't seem to figure it out.
I have a list companies in column A that have a corresponding revenue number in column B.
In column C, I have ANOTHER list of companies and their corresponding revenue number in column D.
Example:
...........Column A.............Column B...........Column C...........Column D
ROW 1: Expedia ..............$3,200..................................................
ROW 2: .................................................Expedia ............$ 5,300..
Desired result:
...........Column A.............Column B...........Column C...........Column D
ROW 1: Expedia ..............$3,200..............Expedia ............$ 5,300..
Any help would be much appreciated.
Thanks
bob
It's a 1 to 1 ratio? Is it always that straightforward? Adjoining rows, same company name? Is there ANY weirdness here to to deal with?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
well you wont get that into a,b,c,d, but in e,f,g,h
put
in E1=A1
IN F1 =B1
IN G1=VLOOKUP($A1,$C:$D,1,FALSE)
in H1=VLOOKUP($A1,$C:$D,2,FALSE)
this assumes
1.no duplicate names in column A
2. all are spelt the same and same length(no additional spaces)
3. they always occur in a and c
then drag down to end of list
Last edited by martindwilson; 12-22-2008 at 07:48 PM.
thanks for the formula, but I'm getting #N/A errors in all the formulas...
that's why I got #N/A errors! ...duh!
I need to build this formula out several more columns to accomodate more data...
If I wanted to make this formula work for (8) sets of data how would the formula change?
Why don't you make is easy on us and post a significant ACTUAL set of data that represents all the needs? Don't pick at the problem, let's look at the real thing.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
An example is attached for your review,
thanks!
-bob
Down and dirty, this will create a master list of vendors and pull over the sales data. Give it a shot, run the macro SortAdv().
If your final sheet is significantly longer in rows, you will need to adjust the NAME RANGE "Table". Click on Insert > Name > Define > click on Table and adjust the range to go down further.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
WOW!
How did you do that?
That is so cool...
I once lived in Bakersfield for one summer...and I never saw ANYTHING that cool before...lol...
Is it possible to add a sort function to this macro so that when it runs it sorts the largest advertisers on top?
Thanks again....WAY exceeded my expectations!!!!
-b
Last edited by shg; 12-23-2008 at 12:02 AM.
Have to add another column for TOTALS on each vendor to get that.
Last edited by JBeaucaire; 12-22-2008 at 11:42 PM. Reason: Corrected first section of macro to include clean out of TOTALS column.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Incredible...
Thank you sir...
Quick question, when i try to run a pivot report the column headers just sazy "Revenue 1, Revenue 2, Revenue 3, etc...
But when i try to lable them Travelocity Revenue, Expedia Revenue, Orbitz Revenue the entire macro erases itself...
Is it possible to re-lable without effecting the Macro?
-b
Just did some double checking to see if it was picking up all the revenue...
I went to the bottom of the macro-produced list and selected "Ziff Davis Media Inc. Sync" which had zero dollars entered across all 8 sites.
I was wondering if was consistent with the MASTER data.
So, i did a Ctrl "F" (FIND) on the term "Ziff Davis Media Inc. Sync" for the entire Document and it found $1,400 under Orbitz (ROW 150).
I did another search for "Accor Accor North America Ibis Hotel" and it found it on ROW 4 of TripAdvisor for $24,300.
Any ideas?
Yes, the INDEX(MATCH()) appears to allow "nearby" matches. That won't do. Let's go back to straight VLOOKUP which allows us to specify exact matches only.
Good catch. Also, I changed the Labels so you can filter.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
but one small tweak...
I need to compare one more site in the list.
I need to add an extra column called "TAN Revenue"
It needs to be inserted between Concierge.com Revenue and TOTAL Revenue columns in the macro result.
In addition, is it possible to add a column at the end of the macro result called "TAN SOW %" ? --- This field would be a field that would calculate the SOW % (Share of Wallet %) for TAn revenue vs. al the other sites.
Example, TAN Revenue for advertiser Walt Disney Resorts was $1,000 vs. $10,000 for all the other sites, thus the TAN SOW % would calculate the number 10%...etc...
I've inserted the raw data into the worksheet for you. I just don't know how to modify the macro to include these new column(s).
See attached.
thanks,
b
This is the first time I've noticed repeated vendors in one column. You have Walt Disney Resorts and Vail Resorts listed verbatim multiple times in the Tan Column.
The sheet is creating a list of unique values and gathering the sales data from the first one it finds. Is the TAN column always going to have redundant vendor names and needs to collate them?
Any other columns going to do that?
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?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks