Solved

Matching Color in two Columns excel 2007

Posted on 2011-09-05
16
390 Views
Last Modified: 2012-05-12
hi,
i'm wondering if this can be done with vba in excel 2007. i've two columns in my sheet1. in column A i've few cells(say about 10 or so) that are colored red and then green (again a few cells) and blue and so on. For every red colored cells from column A, there's a cell in column B, that matches the color with them, for every blue colored cells in column A, there's again a blue colored cell in column B.

what i want is to get a code, that matches all the cells of same color from Column A with the same colored cell of column B and copy that cell from column B to sheet2 Column B respective to the same colored cell from Column A of sheet2.
i hope i didn't messed up.
0
Comment
Question by:NYQuicksale
  • 8
  • 6
16 Comments
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36485045
Might be useful if you provided a small sample workbook of some sample of the data involved, and the desired results.

~bp
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36485097
i didn't make it clear?
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36485146
No, it would be easier to understand with a sample set of data.

On Sheet 1:

It feels like there will be several cells of the same color in column A, but only one of that color in column B, yes?

On Sheet 2:

What data is in column A?

~bp
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36485204
i'm making a sample sheet, taking some tym. will just send u
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36485270
making a sample sheet helped me understanding a bit of what i want.
here'r the three sheets.
Raw BS
Dict
Formatted BS

the Dict contains two columns, A contains all the possible words against one unique color from Column B.
Every cell from column A of sheet Raw BS will be matched with the Sheet Dict column A and if matched then the respective values from column B and C from column A sheet BS will be put in sheet formatted BS(matching the color with column B of sheet Dict).
at this time, i just dont want to touch those values from raw BS, that do not match the colors.

its very complicated. i dont know if that will realy work or not, i'm actually trying different ways, this is one of them.  sample.xlsx
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36485655
Is having the color in the formatted sheet really important, or is this really about merging several different names for the same item to a single item?  For the latter, a simple lookup table approach can be used if we format the table slightly differently.

Here's an example of usage of VLOOKUP to use the Dict table to get a new name for certain lines.

This may not be what you are after, but wanted to share these thoughts in case it was useful. The better we can describe the problem and solution the better the result will be.

~bp
 sample2.xlsx
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36485686
And just to give you some other ideas on what's possible without any programming, here's an example of using conditional formatting to add fill color to certain values.  Notice on the first sheet now some headings have color.  On the Home ribbon tab select the Conditional Formatting, Manage Rules and notice how this was implemented.

Just sharing some approaches that might be useful depending on the effect you are after.

~bp
 sample3.xlsx
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:NYQuicksale
ID: 36488332
hi, thanks for sharing useful ideas, the table approach can also be used, okay then let me give u a sample of it as well.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36488489
hey u said about using formulas? do u think its that simple to use vlookup??
okay then let me tell u what i want. i want to match the column A of Raw BS with the Column A of Dict and if matched, then for each items of cell i've a matched cell in column B (for each set of which i defined separate colors, if u want i can create tables as well, but not sure how to create tables, because i want to create a relation between a set of cells from column A with each cell of Column B) and then finally for the matched items in both columns of sheet Dict, i've to put it in sheet Formatted BS. again i think i didn't make it clear, if so plz tell me, i'll try to futther elaborate it to u.

if using vlookup would have solved the problems, then i would have used it way before, actually using colors is one of the option. its not that i want to conditionally format anything.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36488572
actually it consists of more than one index and match functions, first it has to match the column A of raw bs with column A of Dict, if matched, then it'll check which cells from Column B relates to that cell from column A of Dict sheet. after checking it'll match that cell from column B to the column A of formatted BS. and then finally matched, it'll put that starting raw BS column A cell's correspoding column B value to the column B of formatted BS.

the purpose is to put the values from column B of raw BS to Column B of formatted BS, all this matching stuff is for that purpose.
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36492438
So is the idea that you will have slightly different variations of the Raw sheet, where a few of the column A text titles may be slightly different, and you are trying to transfer the corresponding values from Raw column B and C to the Formatted sheet column B and C, for the "matching" column A value?

In the cases where you have multiple values in the Dict column A that go to a single column B value, will there ever be more than one of those in the Raw sheet being processed? So for example, you have:

Cash
Cash & Cash Equivalents
Cash and Cash Equivalents
Cash Equivalents

all "mapping" to:

Cash and Cash Equivalents

Can there be more than one of the first group, or will there only be one in a specific Raw sheet.

~bp
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36495350
yes, i'm trying to convert all the text in column A of raw sheet in a format defined in column A of formatted sheet based upon the match in Dict sheet.
the idea is in raw sheet, the text can be named differently, so i tried to create a list of as many item names in column A of Dict sheet and listed one unique name against all the possibilities, the names that i enetered in column A of Dict sheet can be increased against every unique item in column  B of Dict sheet.
and yes they all map to cash and cash equivalents in your above example.there will be just one name eith cash or cash & cash equivalent or cash and cash equivalent etc in raw sheet. i'm just trying to create as many possible names as there can be to create a match.
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 36500354
Okay, I think I get this.  Not sure if it can be done without some VBA coding or not, I may reach out to a couple of other experts for opinions.

~bp
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36547284
NYQuickSale, this can be accomplished with a slight transformation of your table (colors help the user, but we'd have to be using Excel VBA or some of the more sophisticated color detection functionality with 2007-2010 to use that), a helper column, and the use of a formula that uses: INDEX, SUMPRODUCT, and some tests for errors.

Sorry for the lengthy post, but I wanted to ensure you and other readers could ascertain what these more sophisticated functions were doing.  If you just want to test them out, see the bolded formulas and/or download the attached sample, then come back to this if you have future problems and need to cogitate a bit on the formula.

The final formula looks like this - pasted in "Formatted BS" tab, cell B3 and copy down and to the right, as far as you need (omitting the underlined header items, I believe :)

To start, while your color coding idea is ingenious, there's extra braincell/coding work to support that.  You can create the index table with slightly more effort, and that alleviates formulas/coding issues.

While Column A describes every combination of text you might be looking for, the "match if it looks like this" column, Column B describes what you want it to mean, what you use in your report.  Rather than using color coding, I filled out a row in Column B (replicating the same term, as necessary) for every row in Column A.

PLEASE ENSURE Column A are EXACT text matches from your report, though I've handled case sensitivity, which was throwing my math off (see PS, below). Note, just to correct for case, we have to change the match function to an array function - costs processing time, but may not be an issue.

PS - this is important, as it can save time, you can use WILDCARD CHARACTERS in Column A.  E.g., Cash* could potentially mean anything that starts with "Cash" and the Column B row would be "Cash and Cash Equivalents".  Just be user that your wildcards don't match something other than what you're defining it to line up - e.g., "Current*" could stand for "Current Assets" as well as "Current Liabilities" so you wouldn't get the results you were looking for if your wildcard setup is not unique to the Column B descriptor.

Once I lined up Column A and B, I created Column C in the Dict tab.  Column C does the work to tell us what ROW in the Raw BS sheet matches, and where data can be found.  I've assumed for this exercise that there won't be repeating elements in Raw BS - otherwise, perhaps VBA functions would be required to return an array of row numbers matchinng (can be done and I have that function written, but taking the simplest approach!).

Here's what that match function looks like - pasted in C2, copied down for your table:

=MATCH(UPPER(A2),UPPER('Raw BS'!A:A),0)
use CTRL-SHIFT-ENTER to confirm the array function for the UPPER conversion.

I then created one final helper, column D2, to check for duplicate "matches", as we'll be using this in a SUMPRODUCT, so don't want to double-count!

=IFERROR(IF(COUNTIF(C$2:$C2,C2)>1,0,C2),0)


With those combined, we have a solution that pulls the data, as required.

In the Formatted BS sheet, we can then use this formula in B3, copying down and across omitting the underlined header lines...

=IFERROR(INDEX('Raw BS'!B:B,SUMPRODUCT(IF(ISNA(Dict!$D$2:$D$13),0,Dict!$D$2:$D$13)*(Dict!$B$2:$B$13=$A3))),0)
This is also an array function, confirmed with CTRL-SHIFT-ENTER

REMEMBER - SPELLING and SPACES are important - very - if the matches, etc., are going to work.  Ensure the Text on your Formatted BS EXACTLY MATCHES the text in the Dict, column B, and that in the Dict, column B - either directly or with wildcards will match with Raw BS as well.  When something doesn't lookup correctly, its 90%+ due to the Q/A you need to do to set this up.

See attached.

Enjoy!

Dave
sample-r1.xlsm
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36550235
thanks dave
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now