Hello guys,
I am both new to excel as I am to this forum. I hope you can all bear with me as I am a complete noob. My situation is as follows.
CROSSMATCH
I have 2 seperate excel files I would like to crossmatch somehow to combine data. One of the files contains our advertisingcosts per client (30.000+ lines), the other contains our revenue per client (4.000+ lines). Both files contain an amount of clients that are mentioned in both, as well as a number of clients that are only part of the indivual document (as shown in example below). What I would like to do is crossmatch both lists with eachother and detect all the Client X (clients that are mentioned in both lists).
Example: Advertisingcosts list contains: Client X: €1 | Client Y: €1
Revenue list contains: Client X: €2 | Client Z: €2,50
This should end up in a sheet that looks somewhat like this:
A1=Client X revenue B1=Client X advertisingcost C1=A1-B1 (ends up being revenue-cost=profit)
A2= B2=Client Y advertisingcost C2=A1-B1 (ends up being no revenue-cost=loss)
A3=Client Y revenue B3= C3=A1-B1 (ends up being revenue-no cost=profit)
Right now I am able to get this data, but this costs me a full day if not more to do this by hand each time using the SUM.IF and then using Conditional formatting to crossmatch by giving each client a different cellcolor. I know this can be done easier using macro's or scripts or something, but my excel experience is way too limited to find out how.
What makes this even more difficult, is that the revenue and advertising costs of Client X for example is not just one line in the advertisingcosts list and one line in the revenuelist, but a SUM.IF outcome of Client X1 untill Client X100-something.
Is there anybody who can give me any insights of the possibilities or can point me into a direction of formulating my question in more excelrelated terms? If would be extremely appreciated.
It will be good if you attach your sample files with some dummy data.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Well, let's keep it a bit simpler to start with. What I basically need, is to be able to do a batch of conditional formatting at once. So I have a list of names with numbers behind them in file 1.
A B
1 NIKE 45
2 ADIDAS 60
3 REEBOK 30
4 K-SWISS 40
5 FRANTIC 24
6 CONVERSE 20
etc..
File 2 is a much bigger list that contains more specified terms of File 1. I want to be able to sort File 2 based on what is shown in table A in File 1. File 2 will look like this:
A B
1 ADIDAS SHOES 4
2 RED K-SWISS HEADBAND 7
3 K-SWISS HEADBAND 6
4 SOCKS K-SWISS 2
5 BASKETBALL ADIDAS 1
6 NIKE 17
7 FOOTBALL NIKE 7
8 REEBOK SWEATPANTS 1
9 CONVERSE TENNIS 2
10 NIKE STORE 12
etc..
File 1 will always remain the same, where File 2 will be different each time. This crossmatching is something I will need to do on a regular basis, with File 1 containing about 2.500 lines and File 2 containing 30.000+. Can anybody help me to use File 1 to quickly sort File 2, without having to manually set each line of conditional formatting (Text contains)?
I would be most grateful!
Kind regards,
Willem
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks