Solved

Searching for data based on a set list in select rows.

Posted on 2014-02-07
8
131 Views
Last Modified: 2014-02-24
Column A is the line number - left most digit.
Column B contains a list of part numbers
Column D contains a list of part numbers to be matched up to those numbers in column B
When those numbers in D match up with numbers in B, that cell in D turns green.

Would it be possible to ask "enter line number" so the search and comparing  only looks at those line numbers (rows)

Thanks for any suggestions.

Column A :
1.1
1.1.1
1.1.2
1.1.3
3.1
3.1.1
3.1.2
3.1.3

Column B
List of part numbers

Column D
477-003
477-004
477-005
477-006
0
Comment
Question by:dgd1212
  • 5
  • 3
8 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39843837
your question is not clear. You are missing items of Col B.


When those numbers in D match up with numbers in B, that cell in D turns green.

this is done in this workbook but for sure wit the data you supplied as there are no values in Col B then there are now no Green Cells.
This is done thru Conditional formatting you select Col D as a whole and choose Manage Rules and click on Add New Rule and from the list choose the last one Use Formula to Determine which cell to format and put this in the formula
=AND(D1=B1,B1<>"",D1<>"")
Press on Format and choose Fill and choose the Green that you want.
press ok till you close Conditional formatting.

Then you mention:


Would it be possible to ask "enter line number" so the search and comparing  only looks at those line numbers (rows)

What Search are you talking about ??  for sure we can do that but it is not clear what you want.

Please clarify.
gowflow
CondFormat.xlsx
0
 

Author Comment

by:dgd1212
ID: 39848837
Column A is the line number. (The item has various parts hence the item has many line X's)

Column B is all the part numbers that make up the part for a line item.

Column D are the part numbers I am looking to compare against all the part numbers for a line item. in this example it is the first item, Line 1

Column D contains part numbers for a particular device that could be on any line of an order.

When each part number of B is compared to D, if they match then that number in D is highlighted.

I hope that helps

Thank you

A              Column B                Column D
1.1              8573088-001            
1.1.1      8573088-013            
1.1.2      8571499-024            8571499-003
1.1.3      8573088-002            8571499-004
1.1.4      8572989-001            8571499-006
1.1.5      8573088-007            8571499-007
1.1.6      8572396-048            8571499-008
1.1.7      8573088-003            8571499-009
1.1.8      8572396-004            8571499-012
1.1.9      8573088-006            8571499-013
1.1.10      8571499-003            8571499-014
1.1.11      8571499-018            8571499-015
1.1.12      8571499-020            8571499-016
1.1.13      8571499-012            8571499-017
1.1.14      8571499-015            8571499-018
1.1.15      8571499-057            8571499-019
1.1.16      8571499-013            8571499-020
1.1.17      8571499-014            8571499-023
1.1.18      8573088-004            8571499-024
1.1.19      8572396-033            8571499-025
                                          8571499-026
                                          8571499-027
                                          8571499-057
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39849936
yes and no

I do not understand Col A
what Col A has to do with comparing an item of B and D ???

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39863195
Any news to explain better or answer my questions in previous comment ?
gowflow
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:dgd1212
ID: 39879919
Remove column A.  As mentioned each cell of D compares itself to every cell in B. When true, that cell in D is highlighted. Thanks
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39880242
ok got it !!!

Is that what you want ?
1) Select Column D (the entire Column)
2) Press on Conditional Formating
3) Manage Rules
4) Create New Rule
5) last item by Formula
6) Paste the below formula in the formula bar
=AND(MATCH(D1,$B:$B,0)<>0,B1<>"",D1<>"")
7) Choose under the button format a color (I choose in the attached file Green)
8) Press OK and Make sure the Range is D:D
9) Exit Conditional formatting.

The result is that if any of the occurrence exist at least once in Col B then the occurrence in col D is highlighted.

Hope this is what you were looking for.
gowflow
CondFormat.xlsx
0
 

Author Closing Comment

by:dgd1212
ID: 39882142
Thanks You! Works as needed.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39882267
Finally !!! glad I could help.
gowflow
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vb.net dynamic formulas in runtime 11 63
Hiding column macro 10 28
Pivot help - Display only Is Not Null 7 16
Excel - Open, modify and save text file 4 0
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

15 Experts available now in Live!

Get 1:1 Help Now