Re: $$Excel-Macros$$ Re: Excel lookup function
Hi Pawan
=VLOOKUP("*Station 18 (Vacuum)",F3:G21,2,FALSE)
It just equates to N/A, have tried the asterisk on both ends.
regards Alan
On Wed, Dec 16, 2015 at 5:11 PM, Pawan Yadav <pwnyadav...@gmail.com> wrote:
> Hi Alan,
>
> Can you provide the formula which you are driving by using wildcard
> ("*Station 18 (Vaccum)*") but not getting solution?
>
> Note: Don't use Lookup function if your list not in ascending order. Use
> Vlookup function with exact match by using wildcard.
>
> Regards,
> Pawan
>
>
> On Tuesday, 1 December 2015 20:53:42 UTC+5:30, Alan Walsh wrote:
>>
>> 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 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 https://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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Excel lookup function
Any luck with a solution on this Paul? regards Alan On Fri, Dec 4, 2015 at 7:18 PM, Alan Walsh <alandeswa...@gmail.com> wrote: > Hi Paul > > Is this impossible to do using just excel? > > Alan > > On Wed, Dec 2, 2015 at 6:31 PM, Alan Walsh <alandeswa...@gmail.com> wrote: > >> Correct >> On 2 Dec 2015 17:02, "Paul Schreiner" <schreiner_p...@att.net> wrote: >> >>> Are you saying that the SQL query returns: >>> >>> Reject counter for Zone 0, Station 12 (CheckPresence) >>> >>> but the Station Name is only: Station 12 (CheckPresence) >>> >>> ?? >>> >>> >>> *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 11:00 AM >>> *Subject:* Re: $$Excel-Macros$$ Excel lookup function >>> >>> Correct, but the row with the matching station name has extra text in it. >>> Regards Alan >>> On 2 Dec 2015 14:42, "Paul Schreiner" <schreiner_p...@att.net> wrote: >>> >>> 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 tha
Re: $$Excel-Macros$$ Excel lookup function
Hi Paul Is this impossible to do using just excel? Alan On Wed, Dec 2, 2015 at 6:31 PM, Alan Walsh <alandeswa...@gmail.com> wrote: > Correct > On 2 Dec 2015 17:02, "Paul Schreiner" <schreiner_p...@att.net> wrote: > >> Are you saying that the SQL query returns: >> >> Reject counter for Zone 0, Station 12 (CheckPresence) >> >> but the Station Name is only: Station 12 (CheckPresence) >> >> ?? >> >> >> *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 11:00 AM >> *Subject:* Re: $$Excel-Macros$$ Excel lookup function >> >> Correct, but the row with the matching station name has extra text in it. >> Regards Alan >> On 2 Dec 2015 14:42, "Paul Schreiner" <schreiner_p...@att.net> wrote: >> >> 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* >> - >> >>
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 NameMon DayMon NightTue DayTue NightWed DayWed NightThur DayThur >> NightFriday DayFriday NightStation 18 (Vaccum)27636323114654175172180208 >> 238Station 12 Height Measure out of range6911685107486052599674 >> >> 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... >> >> vlo
Re: $$Excel-Macros$$ Excel lookup function
Correct, but the row with the matching station name has extra text in it. Regards Alan On 2 Dec 2015 14:42, "Paul Schreiner" <schreiner_p...@att.net> wrote: > 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 Tu
Re: $$Excel-Macros$$ Excel lookup function
Correct On 2 Dec 2015 17:02, "Paul Schreiner" <schreiner_p...@att.net> wrote: > Are you saying that the SQL query returns: > > Reject counter for Zone 0, Station 12 (CheckPresence) > > but the Station Name is only: Station 12 (CheckPresence) > > ?? > > > *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 11:00 AM > *Subject:* Re: $$Excel-Macros$$ Excel lookup function > > Correct, but the row with the matching station name has extra text in it. > Regards Alan > On 2 Dec 2015 14:42, "Paul Schreiner" <schreiner_p...@att.net> wrote: > > 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 >
Re: $$Excel-Macros$$ Excel lookup function
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 NameMon DayMon NightTue DayTue NightWed DayWed NightThur DayThur > NightFriday DayFriday NightStation 18 (Vaccum)27636323114654175172180208 > 238Station 12 Height Measure out of range6911685107486052599674 > > 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" matches > but 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 y
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 NameMon DayMon NightTue DayTue NightWed DayWed NightThur DayThur > NightFriday DayFriday NightStation 18 (Vaccum)27636323114654175172180208 > 238Station 12 Height Measure out of range6911685107486052599674 > > 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" matches > but 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. Po
$$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.
