so now, you have an array of data.
and a query that returns a station name and number.
so what are you looking up?
are you trying to find the row with the matching station name,locate the
matching value and return the day/shift??
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Alan Walsh <alandeswa...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wednesday, December 2, 2015 9:25 AM
Subject: Re: $$Excel-Macros$$ Excel lookup function
Forgot to answer.
"list of reject descriptions"Is that the column titled "Station Name"? Yes
then: the SQL query returns the rejects in "descending order".that's descending
order by COUNT, not description? Yes Regards alan
On Wed, Dec 2, 2015 at 9:12 AM, Alan Walsh <alandeswa...@gmail.com> wrote:
ok here is the full table of sheet 1 to show you.
| Reject counter for Zone 0, Station 18 (Vaccum) | 276 | 363 | 231 | 146 | 54 |
175 | 172 | 180 | 208 | 238 |
| Station 12 Height Measure out of range | 69 | 116 | 85 | 107 | 48 | 60 | 52
| 59 | 96 | 74 |
| Station 12 Inspection part fails | 59 | 112 | 47 | 66 | 21 | 61 | 28 | 24 |
27 | 25 |
| Station 17 (InsertPad) | 8 | 20 | 14 | 12 | 5 | 16 | 15 | 27 | 21 | 22 |
| Station 8 (CheckPresence) | 87 | 133 | 91 | 77 | 19 | 65 | 34 | 37 | 22 | 38 |
| Station 10 () + St 16 Solvent too long | 28 | 12 | 22 | 10 | 12 | 24 | 17 |
24 | 23 | 25 |
| Station 16 (Manage Solvent) | 23 | 15 | 26 | 10 | 17 | 31 | 17 | 30 | 29 | 29
|
| Station 9 (DispensingSolvent) + St 16 Solvent too long | 26 | 10 | 22 | 9 |
10 | 21 | 12 | 25 | 21 | 25 |
| , Station 15 (DispensingSolvent) + St 16 Solvent too long | 24 | 16 | 24 | 9
| 17 | 25 | 16 | 29 | 31 | 27 |
| Station 13 (TubeOrientation) | 3 | 5 | 4 | 3 | | 2 | 1 | 1 | 4 | |
| Station 1 (LoadLumenTube) | 19 | 25 | 21 | 23 | 9 | 23 | 17 | 22 | 22 | 23 |
| Station 5 (LoadTubeClamp) | 23 | 21 | 43 | 23 | 19 | 32 | 30 | 24 | 36 | 31 |
| Transport (KSL) | 50 | 119 | 94 | 42 | 88 | 97 | 45 | 110 | 173 | 127 |
| Station 6 (CtrlPresPosClamp) | | 2 | 1 | 1 | 1 | 0 | 3 | 1 | 13 | 24 |
| St12: Connector not detected by Sensor | 382 | 107 | 85 | 112 | 56 | 49 | 2 |
9 | 23 | 26 |
| Station 12 (CheckPresence) | 2 | 1 | 0 | 0 | 0 | 2 | 82 | 61 | 115 | 3
|
The example I showed was just showing 2 rows.
On Tue, Dec 1, 2015 at 6:45 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
Still trying to match up you narrative description with your example.
"list of reject descriptions"Is that the column titled "Station Name"?
then: the SQL query returns the rejects in "descending order".that's descending
order by COUNT, not description?
If so, I get that for Station 18, count 172 is found on Thursday, Day shift.But
I don't find the Station 12 count of 82 in your example... so perhaps I'm not
interpreting it correctly.
and, what is it you're trying to look up?
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Alan Walsh <alandeswa...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Tuesday, December 1, 2015 1:25 PM
Subject: Re: $$Excel-Macros$$ Excel lookup function
ok as simple as I can make it.
On the first sheet I have a list of reject descriptions, across which is
populated the number of rejects per shift.(see above)
The second sheet is is from a sql query that returns the rejects in descending
order. (so the second sheet is not in the same order as the first sheet).
hence the example I showed shows the second highest reject count from the
second sheet is not in the same position as the second highest of the first
sheet.
So as the days move on the positions of the reject stations will be different
on the second sheet.but the first sheet will remain the same.
i thought this would be simple to do.
I thought all i had to do was look for the station name from the first sheet
and find that text within the array of the second sheet (lookup_vector) and the
result_vector would give me the number of rejects for that shift.
Sounds easy no?
Alan
On Tue, Dec 1, 2015 at 3:49 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
I'm not sure what you're "looking up"??
Your sample data is:
| Station Name | Mon Day | Mon Night | Tue Day | Tue Night | Wed Day | Wed
Night | Thur Day | Thur Night | Friday Day | Friday Night |
| Station 18 (Vaccum) | 276 | 363 | 231 | 146 | 54 | 175 | 172 | 180 | 208 |
238 |
| Station 12 Height Measure out of range | 69 | 116 | 85 | 107 | 48 | 60 | 52 |
59 | 96 | 74 |
and you said you're looking up:Reject counter for Zone 0, Station 18 (Vaccum)
172
Reject counter for Zone 0, Station 12 (CheckPresence) 82
but why would it return 172 and 82?I see a 172 for Thursday Day shift,but I
don't see a value for 82
As for your wildcards in your vlookup...
vlookup syntax is:VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
If you use Range_Lookup as "true", it will allow "approximate" matchesbut if
you use this, your list must be in "ascending" order.
so Station 12 must come before Station 18.
but.. I'm not sure at all that I understand what you're trying to accomplish.
you said you "pull these faults every 12 hours from the database into an excel
sheet"
then you "manually update these"
update what?
do you mean you READ the values from the database and update the excel sheet
manually?then what does the VLookup have to do with anything?
Could you share a file showing what you START with, the steps you do manually,
and what you want to end up with?
What is your database? msAccess? something else?
how do you read from it?Do you generate a report electronically?
I think we need more detail to help properly.
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Alan Walsh <alandeswa...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tuesday, December 1, 2015 10:23 AM
Subject: $$Excel-Macros$$ Excel lookup function
Hi All
problem description. I have an excel sheet that i update manually. I want to
update automatically.
Here is what I have, A list of faults in a column, across each row is the day
and night shift, Monday to Friday.
see below.
Sheet 1
|
| Station Name | Mon Day | Mon Night | Tue Day | Tue Night | Wed Day | Wed
Night | Thur Day | Thur Night | Friday Day | Friday Night |
| Station 18 (Vaccum) | 276 | 363 | 231 | 146 | 54 | 175 | 172 | 180 | 208 |
238 |
| Station 12 Height Measure out of range | 69 | 116 | 85 | 107 | 48 | 60 | 52
| 59 | 96 | 74 |
I pull these faults every 12 hours from the database into an excel sheet. i
then manually update these.
Sheet 2
|
|
|
|
|
|
|
|
|
|
|
| Reject counter for Zone 0, Station 18 (Vaccum)
172
Reject counter for Zone 0, Station 12 (CheckPresence) 82
I want to set up the lookup function in the first sheet, for each shift and
query the new data, I know this could be done in VB, but I am not that well up
on VB.
even now when i run the lookup function i set my lookup value to "*Station 18
(Vaccum)*" It did not work. the Astrix is for wild card yes? any help on this
would be deadly....
Alan
|
|
|
|
|
|
|
|
|
|
|
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to a topic in the Google
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/excel-macros/DDIG7uFBAHM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to a topic in the Google
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/excel-macros/DDIG7uFBAHM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
