Ordering range entries by certain criteria

[Babymech]

I've got a fairly basic problem that I've run up against a number of times
and never satisfactorily solved. Basically I want to fill a column C with the
entries from column A if Column B fills a certain criterion. To give an
example, I want to be able to list in column C all 'Names' that are listed as
'internal' Type-wise.

Name Type Internal External
1 NameA Internal NameA NameB
2 NameB External NameE NameC
3 NameC External
4 NameD Misc.
5 NameE Internal

The Name and Type columns will extend indefinitely downwards; I would also
want to create a column for Misc. and others of course. Ideally I
want to do this without having to resort to using a hidden column for each
Type, since there are very many possible Types. If I do have to use a hidden
column, how would that be done in the best way?

Thanks!

 

[Gary''s Student]

Say the data in columns A & B is:

Name Type
NameA External
NameB External
NameC Misc
NameD Internal
NameE Misc
NameF Misc
NameG External
NameH External
NameI External
NameJ External
NameK Internal
NameL Misc
NameM External
NameN Internal
NameO Internal
NameP New
NameQ External
NameR Misc
NameS New
NameT Internal
NameU Misc
NameV Misc
NameW Old
NameX Old
NameY Internal
NameZ External

Run this small macro:

Sub Tablify()
' gsnuxx
Dim AlReadyThere As Boolean
n = Cells(Rows.Count, "B").End(xlUp).Row
Cells(1, 3).Value = Cells(2, 2).Value
kHead = 4
For i = 3 To n
v = Cells(i, "B").Value
AlReadyThere = False
For j = 3 To kHead - 1
If v = Cells(1, j) Then
AlReadyThere = True
End If
Next
If Not AlReadyThere Then
Cells(1, kHead) = v
kHead = kHead + 1
End If
Next

For i = 3 To kHead - 1
j = 2
v = Cells(1, i).Value
For k = 2 To n
If Cells(k, "B").Value = v Then
Cells(j, i).Value = Cells(k, "A").Value
j = j + 1
End If
Next
Next
End Sub

It will produce this result, using columns C thru G:

Name Type External Misc Internal New Old
NameA External NameA NameC NameD NameP NameW
NameB External NameB NameE NameK NameS NameX
NameC Misc NameG NameF NameN
NameD Internal NameH NameL NameO
NameE Misc NameI NameR NameT
NameF Misc NameJ NameU NameY
NameG External NameM NameV
NameH External NameQ
NameI External NameZ
NameJ External
NameK Internal
NameL Misc
NameM External
NameN Internal
NameO Internal
NameP New
NameQ External
NameR Misc
NameS New
NameT Internal
NameU Misc
NameV Misc
NameW Old
NameX Old
NameY Internal
NameZ External

 

[Babymech]

That's much much more impressive than I'd hoped for, unfortunately it's
somewhat over my head, since I've never worked with Macros or VBA per se...
I'm trying to reverse engineer your answer but am completely lost on the
terms that are used to control its functions... would it be possible to alter
the macro to output the results in another sheet of the same Excel file? I
didn't mention it earlier, because I wanted to keep my description simple,
but I am hoping to have one sheet in the file be the input sheet and the next
be the summary sheet - would that be possible with this macro?

Thanks!

 

[Gary''s Student]

Discard the previous version.

This is the new version:

Sub Tablify()
' gsnuxx
Dim s2 As Worksheet
Set s2 = Sheets("Sheet2")
Dim AlReadyThere As Boolean
n = Cells(Rows.Count, "B").End(xlUp).Row
s2.Cells(1, 3).Value = Cells(2, 2).Value
kHead = 4
For i = 3 To n
v = Cells(i, "B").Value
AlReadyThere = False
For j = 3 To kHead - 1
If v = s2.Cells(1, j) Then
AlReadyThere = True
End If
Next
If Not AlReadyThere Then
s2.Cells(1, kHead) = v
kHead = kHead + 1
End If
Next

For i = 3 To kHead - 1
j = 2
v = s2.Cells(1, i).Value
For k = 2 To n
If Cells(k, "B").Value = v Then
s2.Cells(j, i).Value = Cells(k, "A").Value
j = j + 1
End If
Next
Next
End Sub

It reads the data from the active sheet and writes the results to "Sheet2".


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Herbert Seidenberg]

Excel 2007
No macros, no VBA, no hidden columns/rows, no formulas:
http://www.mediafire.com/file/mhtm1zqjzyz/12_02_08.xlsx

 

[Babymech]

Excellent, thanks. I still see a lot I'd want to customize further, but
you've given me a very good starting point for learning how to do that, as
well as answered my questions. Thanks again for the help!