ExcelBanter - Looking up data

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Looking up data (https://www.excelbanter.com/excel-discussion-misc-queries/243984-looking-up-data.html)

I hope someone can help me on this:
I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically appears after I typed the part #.

P.S. Please don't tell me just what you would do (macro, v-lookup, etc) but
show me what you did. I have no idea how to start.

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim res As Variant

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'sort original range by Id, name, period
With .Range("a1:B" & LastRow)
.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
header:=xlYes
End With

'Get a list of unique contact types
.Range("b1:b" & LastRow).AdvancedFilter _
action:=xlFilterCopy, unique:=True, copytorange:=NewWks.Range("A1")
End With

With NewWks
With .Range("a:a")
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With

.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Copy
.Range("b1").PasteSpecial Transpose:=True
.Columns(1).Clear
.Range("A1").Value = "Part #"
End With

With CurWks
oRow = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value < .Cells(iRow - 1, "A").Value Then
'different EE#
oRow = oRow + 1
'new EE# in column A
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
End If
oCol = Application.Match(.Cells(iRow, "B").Value, NewWks.Rows(1), 0)
If IsError(oCol) Then
'this shouldn't happen
MsgBox "Error with row: " & iRow
Exit Sub
Else
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "B").Value
End If
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub

Byron720 wrote:

I hope someone can help me on this:
I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically appears after I typed the part #.

P.S. Please don't tell me just what you would do (macro, v-lookup, etc) but
show me what you did. I have no idea how to start.

--

Dave Peterson

Byron720 wrote:

No need to post the question again. You had at least one answer when you
re-posted. Now there are at least two.

OK, I believe we are talking Hall of Fame here and I'm just a rookie. Where
am I supposed to type all this?

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim res As Variant

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'sort original range by Id, name, period
With .Range("a1:B" & LastRow)
.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
header:=xlYes
End With

'Get a list of unique contact types
.Range("b1:b" & LastRow).AdvancedFilter _
action:=xlFilterCopy, unique:=True, copytorange:=NewWks.Range("A1")
End With

With NewWks
With .Range("a:a")
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With

.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Copy
.Range("b1").PasteSpecial Transpose:=True
.Columns(1).Clear
.Range("A1").Value = "Part #"
End With

With CurWks
oRow = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value < .Cells(iRow - 1, "A").Value Then
'different EE#
oRow = oRow + 1
'new EE# in column A
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
End If
oCol = Application.Match(.Cells(iRow, "B").Value, NewWks.Rows(1), 0)
If IsError(oCol) Then
'this shouldn't happen
MsgBox "Error with row: " & iRow
Exit Sub
Else
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "B").Value
End If
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub

Byron720 wrote:

I hope someone can help me on this:
I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically appears after I typed the part #.

P.S. Please don't tell me just what you would do (macro, v-lookup, etc) but
show me what you did. I have no idea how to start.

--

Dave Peterson

Thanks and I know. It's just that the first time I posted the expert just
told me what he would do and nothing else. That's why this one has a PS at
the end. Still, I can go with your answer on Q1.

"smartin" wrote:

Byron720 wrote:

No need to post the question again. You had at least one answer when you
re-posted. Now there are at least two.

Try this...

Sheet1 A2:An = PN's
Sheet1 B2:Bn = bin locations

Part refers to Sheet1!$A$2:$A$n
Bin refers to Sheet1!$B$2:$B$n

On Sheet2...

A2 = some PN

Enter this formula in B2. This will return how many bin locations there are
for the PN.

=COUNTIF(Part,A2)

Enter this array formula** in C2. This will return the bin locations.

=IF(COLUMNS($C2:C2)$B2,"",INDEX(Bin,SMALL(IF(Part =$A2,ROW(Bin)),COLUMNS($C2:C2))-MIN(ROW(Bin))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy across until you get blanks

--
Biff
Microsoft Excel MVP

"Byron720" wrote in message
...
I hope someone can help me on this:
I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master
data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically appears after I typed the part #.

P.S. Please don't tell me just what you would do (macro, v-lookup, etc)
but
show me what you did. I have no idea how to start.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Byron720 wrote:

OK, I believe we are talking Hall of Fame here and I'm just a rookie. Where
am I supposed to type all this?

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim res As Variant

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'sort original range by Id, name, period
With .Range("a1:B" & LastRow)
.Sort key1:=.Columns(1), order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
header:=xlYes
End With

'Get a list of unique contact types
.Range("b1:b" & LastRow).AdvancedFilter _
action:=xlFilterCopy, unique:=True, copytorange:=NewWks.Range("A1")
End With

With NewWks
With .Range("a:a")
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With

.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Copy
.Range("b1").PasteSpecial Transpose:=True
.Columns(1).Clear
.Range("A1").Value = "Part #"
End With

With CurWks
oRow = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value < .Cells(iRow - 1, "A").Value Then
'different EE#
oRow = oRow + 1
'new EE# in column A
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
End If
oCol = Application.Match(.Cells(iRow, "B").Value, NewWks.Rows(1), 0)
If IsError(oCol) Then
'this shouldn't happen
MsgBox "Error with row: " & iRow
Exit Sub
Else
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, "B").Value
End If
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub

Byron720 wrote:

I hope someone can help me on this:
I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically appears after I typed the part #.

P.S. Please don't tell me just what you would do (macro, v-lookup, etc) but
show me what you did. I have no idea how to start.

--

Dave Peterson

--

Dave Peterson