| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up multiple values with in the same number
I am trying to pull data out of a database dump that looks like this
76076 SWLZ SEC1 EA 4 76076 SWLZ SEC2 EA 4 76076 SWLZ SEC3 EA 4 76076 SWLZ SEC4 EA 4 76076 SWLZ SEC5 EA 4 76156 SWLZ SEC1 EA 1 76156 SWLZ SEC2 EA 1 76156 SWLZ SEC3 EA 1 76156 SWLZ SEC4 EA 1 76156 SWLZ SEC5 EA 1 76165 SWLZ SEC1 EA 1 76165 SWLZ SEC2 EA 1 76165 SWLZ SEC3 EA 1 76165 SWLZ SEC4 EA 1 76165 SWLZ SEC5 EA 1 76231 SWLZ SEC1 EA 6 76231 SWLZ SEC2 EA 6 76231 SWLZ SEC3 EA 6 76231 SWLZ SEC4 EA 6 76231 SWLZ SEC5 EA 6 76232 SWLZ SEC1 EA 6 76232 SWLZ SEC2 EA 6 76232 SWLZ SEC3 EA 6 76232 SWLZ SEC4 EA 6 76232 SWLZ SEC5 EA 6 76233 SWLZ SEC1 EA 1 76233 SWLZ SEC2 EA 1 76233 SWLZ SEC3 EA 1 76233 SWLZ SEC4 EA 1 76233 SWLZ SEC5 EA 1 76431 SWLZ SEC1 EA 1 76431 SWLZ SEC2 EA 1 76431 SWLZ SEC3 EA 1 76431 SWLZ SEC4 EA 1 76431 SWLZ SEC5 EA 1 76432 SWLZ SEC1 EA 5 76432 SWLZ SEC2 EA 5 76432 SWLZ SEC3 EA 5 76432 SWLZ SEC4 EA 5 76432 SWLZ SEC5 EA 5 76433 SWLZ SEC1 EA 5 76433 SWLZ SEC2 EA 5 76433 SWLZ SEC3 EA 5 76433 SWLZ SEC4 EA 5 76433 SWLZ SEC5 EA 5 76434 SWLZ SEC1 EA 2 76434 SWLZ SEC2 EA 2 76434 SWLZ SEC3 EA 2 76434 SWLZ SEC4 EA 2 76434 SWLZ SEC5 EA 2 76435 SWLZ SEC1 EA 2 76435 SWLZ SEC2 EA 2 There are 5 locations SEC1-SEC5 that all carry the same sku just in a different physical location. I would like to create 5 colums that looks like this 76076 SEC1 4 76156 SEC1 1 76165 SEC1 1 76231 SEC1 6 76232 SEC1 6 76233 SEC1 1 76431 SEC1 1 76432 SEC1 5 76433 SEC1 5 I dont think I can explain it fully, I would love to email someone the workbook and let them see what I am trying to work with. It will be a very dumbed down workbook, not the original. I will show you the combination of IF-AND-VLOOKUP that worked but will only pull the 1st occurrence of the number. Please let me know if anybody can help. I have never been this stumped over something I can not figure out in Excel. Thanks. -Charlie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up multiple values with in the same number
Consider using AutoFilter. AutoFiltering your posted on SEC1 yields:
76076 SWLZ SEC1 EA 4 76156 SWLZ SEC1 EA 1 76165 SWLZ SEC1 EA 1 76231 SWLZ SEC1 EA 6 76232 SWLZ SEC1 EA 6 76233 SWLZ SEC1 EA 1 76431 SWLZ SEC1 EA 1 76432 SWLZ SEC1 EA 5 76433 SWLZ SEC1 EA 5 76434 SWLZ SEC1 EA 2 76435 SWLZ SEC1 EA 2 Is this good enough? -- Gary''s Student - gsnu200810 "Charlie" wrote: I am trying to pull data out of a database dump that looks like this 76076 SWLZ SEC1 EA 4 76076 SWLZ SEC2 EA 4 76076 SWLZ SEC3 EA 4 76076 SWLZ SEC4 EA 4 76076 SWLZ SEC5 EA 4 76156 SWLZ SEC1 EA 1 76156 SWLZ SEC2 EA 1 76156 SWLZ SEC3 EA 1 76156 SWLZ SEC4 EA 1 76156 SWLZ SEC5 EA 1 76165 SWLZ SEC1 EA 1 76165 SWLZ SEC2 EA 1 76165 SWLZ SEC3 EA 1 76165 SWLZ SEC4 EA 1 76165 SWLZ SEC5 EA 1 76231 SWLZ SEC1 EA 6 76231 SWLZ SEC2 EA 6 76231 SWLZ SEC3 EA 6 76231 SWLZ SEC4 EA 6 76231 SWLZ SEC5 EA 6 76232 SWLZ SEC1 EA 6 76232 SWLZ SEC2 EA 6 76232 SWLZ SEC3 EA 6 76232 SWLZ SEC4 EA 6 76232 SWLZ SEC5 EA 6 76233 SWLZ SEC1 EA 1 76233 SWLZ SEC2 EA 1 76233 SWLZ SEC3 EA 1 76233 SWLZ SEC4 EA 1 76233 SWLZ SEC5 EA 1 76431 SWLZ SEC1 EA 1 76431 SWLZ SEC2 EA 1 76431 SWLZ SEC3 EA 1 76431 SWLZ SEC4 EA 1 76431 SWLZ SEC5 EA 1 76432 SWLZ SEC1 EA 5 76432 SWLZ SEC2 EA 5 76432 SWLZ SEC3 EA 5 76432 SWLZ SEC4 EA 5 76432 SWLZ SEC5 EA 5 76433 SWLZ SEC1 EA 5 76433 SWLZ SEC2 EA 5 76433 SWLZ SEC3 EA 5 76433 SWLZ SEC4 EA 5 76433 SWLZ SEC5 EA 5 76434 SWLZ SEC1 EA 2 76434 SWLZ SEC2 EA 2 76434 SWLZ SEC3 EA 2 76434 SWLZ SEC4 EA 2 76434 SWLZ SEC5 EA 2 76435 SWLZ SEC1 EA 2 76435 SWLZ SEC2 EA 2 There are 5 locations SEC1-SEC5 that all carry the same sku just in a different physical location. I would like to create 5 colums that looks like this 76076 SEC1 4 76156 SEC1 1 76165 SEC1 1 76231 SEC1 6 76232 SEC1 6 76233 SEC1 1 76431 SEC1 1 76432 SEC1 5 76433 SEC1 5 I dont think I can explain it fully, I would love to email someone the workbook and let them see what I am trying to work with. It will be a very dumbed down workbook, not the original. I will show you the combination of IF-AND-VLOOKUP that worked but will only pull the 1st occurrence of the number. Please let me know if anybody can help. I have never been this stumped over something I can not figure out in Excel. Thanks. -Charlie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up multiple values with in the same number
Hi,
To achive your example blw: (F)76076 (G)SEC1 (H)4 (F)(G)(H) being the columns write the formulas blw =IF(C1="SEC1",A1,"") =IF(C1="SEC1",C1,"") =IF(E1="SEC1",E1,"") For each column I would do: (A)76076 (B)SWLZ (C)SEC1 (D)EA (E)4 then you can write a macro to clear the content (see blw) then select, copy, paste and delete the empty cells With Range("F1:H40") .NumberFormat = "General" .Value = .Value End With Ciao Arno "Charlie" wrote: I am trying to pull data out of a database dump that looks like this 76076 SWLZ SEC1 EA 4 76076 SWLZ SEC2 EA 4 76076 SWLZ SEC3 EA 4 76076 SWLZ SEC4 EA 4 76076 SWLZ SEC5 EA 4 76156 SWLZ SEC1 EA 1 76156 SWLZ SEC2 EA 1 76156 SWLZ SEC3 EA 1 76156 SWLZ SEC4 EA 1 76156 SWLZ SEC5 EA 1 76165 SWLZ SEC1 EA 1 76165 SWLZ SEC2 EA 1 76165 SWLZ SEC3 EA 1 76165 SWLZ SEC4 EA 1 76165 SWLZ SEC5 EA 1 76231 SWLZ SEC1 EA 6 76231 SWLZ SEC2 EA 6 76231 SWLZ SEC3 EA 6 76231 SWLZ SEC4 EA 6 76231 SWLZ SEC5 EA 6 76232 SWLZ SEC1 EA 6 76232 SWLZ SEC2 EA 6 76232 SWLZ SEC3 EA 6 76232 SWLZ SEC4 EA 6 76232 SWLZ SEC5 EA 6 76233 SWLZ SEC1 EA 1 76233 SWLZ SEC2 EA 1 76233 SWLZ SEC3 EA 1 76233 SWLZ SEC4 EA 1 76233 SWLZ SEC5 EA 1 76431 SWLZ SEC1 EA 1 76431 SWLZ SEC2 EA 1 76431 SWLZ SEC3 EA 1 76431 SWLZ SEC4 EA 1 76431 SWLZ SEC5 EA 1 76432 SWLZ SEC1 EA 5 76432 SWLZ SEC2 EA 5 76432 SWLZ SEC3 EA 5 76432 SWLZ SEC4 EA 5 76432 SWLZ SEC5 EA 5 76433 SWLZ SEC1 EA 5 76433 SWLZ SEC2 EA 5 76433 SWLZ SEC3 EA 5 76433 SWLZ SEC4 EA 5 76433 SWLZ SEC5 EA 5 76434 SWLZ SEC1 EA 2 76434 SWLZ SEC2 EA 2 76434 SWLZ SEC3 EA 2 76434 SWLZ SEC4 EA 2 76434 SWLZ SEC5 EA 2 76435 SWLZ SEC1 EA 2 76435 SWLZ SEC2 EA 2 There are 5 locations SEC1-SEC5 that all carry the same sku just in a different physical location. I would like to create 5 colums that looks like this 76076 SEC1 4 76156 SEC1 1 76165 SEC1 1 76231 SEC1 6 76232 SEC1 6 76233 SEC1 1 76431 SEC1 1 76432 SEC1 5 76433 SEC1 5 I dont think I can explain it fully, I would love to email someone the workbook and let them see what I am trying to work with. It will be a very dumbed down workbook, not the original. I will show you the combination of IF-AND-VLOOKUP that worked but will only pull the 1st occurrence of the number. Please let me know if anybody can help. I have never been this stumped over something I can not figure out in Excel. Thanks. -Charlie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up multiple values with in the same number
Sorry last formula amended
=IF(C1="SEC1",A1,"") =IF(C1="SEC1",C1,"") =IF(C1="SEC1",E1,"") "Arno" wrote: Hi, To achive your example blw: (F)76076 (G)SEC1 (H)4 (F)(G)(H) being the columns write the formulas blw =IF(C1="SEC1",A1,"") =IF(C1="SEC1",C1,"") =IF(E1="SEC1",E1,"") For each column I would do: (A)76076 (B)SWLZ (C)SEC1 (D)EA (E)4 then you can write a macro to clear the content (see blw) then select, copy, paste and delete the empty cells With Range("F1:H40") .NumberFormat = "General" .Value = .Value End With Ciao Arno "Charlie" wrote: I am trying to pull data out of a database dump that looks like this 76076 SWLZ SEC1 EA 4 76076 SWLZ SEC2 EA 4 76076 SWLZ SEC3 EA 4 76076 SWLZ SEC4 EA 4 76076 SWLZ SEC5 EA 4 76156 SWLZ SEC1 EA 1 76156 SWLZ SEC2 EA 1 76156 SWLZ SEC3 EA 1 76156 SWLZ SEC4 EA 1 76156 SWLZ SEC5 EA 1 76165 SWLZ SEC1 EA 1 76165 SWLZ SEC2 EA 1 76165 SWLZ SEC3 EA 1 76165 SWLZ SEC4 EA 1 76165 SWLZ SEC5 EA 1 76231 SWLZ SEC1 EA 6 76231 SWLZ SEC2 EA 6 76231 SWLZ SEC3 EA 6 76231 SWLZ SEC4 EA 6 76231 SWLZ SEC5 EA 6 76232 SWLZ SEC1 EA 6 76232 SWLZ SEC2 EA 6 76232 SWLZ SEC3 EA 6 76232 SWLZ SEC4 EA 6 76232 SWLZ SEC5 EA 6 76233 SWLZ SEC1 EA 1 76233 SWLZ SEC2 EA 1 76233 SWLZ SEC3 EA 1 76233 SWLZ SEC4 EA 1 76233 SWLZ SEC5 EA 1 76431 SWLZ SEC1 EA 1 76431 SWLZ SEC2 EA 1 76431 SWLZ SEC3 EA 1 76431 SWLZ SEC4 EA 1 76431 SWLZ SEC5 EA 1 76432 SWLZ SEC1 EA 5 76432 SWLZ SEC2 EA 5 76432 SWLZ SEC3 EA 5 76432 SWLZ SEC4 EA 5 76432 SWLZ SEC5 EA 5 76433 SWLZ SEC1 EA 5 76433 SWLZ SEC2 EA 5 76433 SWLZ SEC3 EA 5 76433 SWLZ SEC4 EA 5 76433 SWLZ SEC5 EA 5 76434 SWLZ SEC1 EA 2 76434 SWLZ SEC2 EA 2 76434 SWLZ SEC3 EA 2 76434 SWLZ SEC4 EA 2 76434 SWLZ SEC5 EA 2 76435 SWLZ SEC1 EA 2 76435 SWLZ SEC2 EA 2 There are 5 locations SEC1-SEC5 that all carry the same sku just in a different physical location. I would like to create 5 colums that looks like this 76076 SEC1 4 76156 SEC1 1 76165 SEC1 1 76231 SEC1 6 76232 SEC1 6 76233 SEC1 1 76431 SEC1 1 76432 SEC1 5 76433 SEC1 5 I dont think I can explain it fully, I would love to email someone the workbook and let them see what I am trying to work with. It will be a very dumbed down workbook, not the original. I will show you the combination of IF-AND-VLOOKUP that worked but will only pull the 1st occurrence of the number. Please let me know if anybody can help. I have never been this stumped over something I can not figure out in Excel. Thanks. -Charlie |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up multiple values with in the same number
I dont want to have to manipulate the data everytime, i want to pull data
into a sheet without touching the database sheet. not in the example but the database sheet remains a sheet that is of an entirely different file. i also have all of the numbers in 5 different rows. SEC 1 SEC 2 SEC 3 SEC 4 SEC 5 I guess I should have explained it this way too... I have 3 sheets, from the bottom up it will be the database dump that is unsorted, to a sheet that pulls all the information and sorts it like my example directly above and then to the top sheet which is the only sheet I would like to have to look at that formats it and everything so it can be presented to my people. The database dump gets run a few times a day and is very time consuming, I just want to be able to run the database dump and replace the 1 file, hit F9 for the update in excel and be done. "Gary''s Student" wrote: Consider using AutoFilter. AutoFiltering your posted on SEC1 yields: 76076 SWLZ SEC1 EA 4 76156 SWLZ SEC1 EA 1 76165 SWLZ SEC1 EA 1 76231 SWLZ SEC1 EA 6 76232 SWLZ SEC1 EA 6 76233 SWLZ SEC1 EA 1 76431 SWLZ SEC1 EA 1 76432 SWLZ SEC1 EA 5 76433 SWLZ SEC1 EA 5 76434 SWLZ SEC1 EA 2 76435 SWLZ SEC1 EA 2 Is this good enough? -- Gary''s Student - gsnu200810 "Charlie" wrote: I am trying to pull data out of a database dump that looks like this 76076 SWLZ SEC1 EA 4 76076 SWLZ SEC2 EA 4 76076 SWLZ SEC3 EA 4 76076 SWLZ SEC4 EA 4 76076 SWLZ SEC5 EA 4 76156 SWLZ SEC1 EA 1 76156 SWLZ SEC2 EA 1 76156 SWLZ SEC3 EA 1 76156 SWLZ SEC4 EA 1 76156 SWLZ SEC5 EA 1 76165 SWLZ SEC1 EA 1 76165 SWLZ SEC2 EA 1 76165 SWLZ SEC3 EA 1 76165 SWLZ SEC4 EA 1 76165 SWLZ SEC5 EA 1 76231 SWLZ SEC1 EA 6 76231 SWLZ SEC2 EA 6 76231 SWLZ SEC3 EA 6 76231 SWLZ SEC4 EA 6 76231 SWLZ SEC5 EA 6 76232 SWLZ SEC1 EA 6 76232 SWLZ SEC2 EA 6 76232 SWLZ SEC3 EA 6 76232 SWLZ SEC4 EA 6 76232 SWLZ SEC5 EA 6 76233 SWLZ SEC1 EA 1 76233 SWLZ SEC2 EA 1 76233 SWLZ SEC3 EA 1 76233 SWLZ SEC4 EA 1 76233 SWLZ SEC5 EA 1 76431 SWLZ SEC1 EA 1 76431 SWLZ SEC2 EA 1 76431 SWLZ SEC3 EA 1 76431 SWLZ SEC4 EA 1 76431 SWLZ SEC5 EA 1 76432 SWLZ SEC1 EA 5 76432 SWLZ SEC2 EA 5 76432 SWLZ SEC3 EA 5 76432 SWLZ SEC4 EA 5 76432 SWLZ SEC5 EA 5 76433 SWLZ SEC1 EA 5 76433 SWLZ SEC2 EA 5 76433 SWLZ SEC3 EA 5 76433 SWLZ SEC4 EA 5 76433 SWLZ SEC5 EA 5 76434 SWLZ SEC1 EA 2 76434 SWLZ SEC2 EA 2 76434 SWLZ SEC3 EA 2 76434 SWLZ SEC4 EA 2 76434 SWLZ SEC5 EA 2 76435 SWLZ SEC1 EA 2 76435 SWLZ SEC2 EA 2 There are 5 locations SEC1-SEC5 that all carry the same sku just in a different physical location. I would like to create 5 colums that looks like this 76076 SEC1 4 76156 SEC1 1 76165 SEC1 1 76231 SEC1 6 76232 SEC1 6 76233 SEC1 1 76431 SEC1 1 76432 SEC1 5 76433 SEC1 5 I dont think I can explain it fully, I would love to email someone the workbook and let them see what I am trying to work with. It will be a very dumbed down workbook, not the original. I will show you the combination of IF-AND-VLOOKUP that worked but will only pull the 1st occurrence of the number. Please let me know if anybody can help. I have never been this stumped over something I can not figure out in Excel. Thanks. -Charlie |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up multiple values with in the same number
Arno,
I am having issues implementing your way of doing it, is it possible i could send you the excel sheet? There are no macros or anything in it of security risk. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up multiple values with in the same number
|
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up multiple values with in the same number
I also want to note that if there is QTY 0 of a Material Number in a location
it will not show up as being 0 it will just not be there at all. So some will have SEC1 SEC2 SEC3 SEC5 giving the example that there is QTY 0 of a material in SEC4 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
| How do I return multiple values from a single number in a list? | Excel Discussion (Misc queries) | |||
| Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
| max number based on multiple values | Excel Discussion (Misc queries) | |||
| Values from website turned into useable number values | Excel Worksheet Functions | |||
Linear Mode
