Looking up multiple values with in the same number

[Charlie]

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

 

[Gary''s Student]

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?

 

[Arno]

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

 

[Arno]

Sorry last formula amended
=IF(C1="SEC1",A1,"") =IF(C1="SEC1",C1,"") =IF(C1="SEC1",E1,"")

 

[Charlie]

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.

 

[Charlie]

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.

 

[Arno]

....sure, you can send the file to the following email:

(e-mail address removed)

this evening I will fix it and send it to you, no prob

 

[Charlie]

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