array?

jimtheeagle

New Member
Joined
Jan 15, 2013
Messages
31
Hi

I have a reference tab that looks like this:

DCN
DCNROFO
EMEROFO
EME
EME
HOU
HOUROFR

<tbody>
</tbody>

A report tab will pull from the above and look like this:

DCNROFO
EMEROFO
HOUROFR

<tbody>
</tbody>

Is there a simple way to do this?

Thanks
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Can you sort reference tab by column B? Then a vlookup works.
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
Maybe something like....

Formula is D2

=IFERROR(INDEX(A$1:A$7,SMALL(IF($B$1:$B$7<>"",ROW($A$1:$A$7)-ROW($A$1)+1),ROWS($D$1:D1))),"") Control Shift Enter

dragged over and down



A
B
C
D
E
1
DCN
DCN
ROFO
2
DCN
ROFO
EME
ROFO
3
EME
ROFO
HOU
ROFR
4
EME
5
EME
6
HOU
7
HOU
ROFR

<TBODY>
</TBODY>
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

jimtheeagle,

You did state that you had two worksheets.

How about a macro solution?

Sample raw data in worksheet Reference:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">DCN</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">DCN</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">ROFO</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">EME</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">ROFO</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">EME</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">EME</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">HOU</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">HOU</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">ROFR</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Reference</p><br /><br />

After the macro (using two arrays in memory) in worksheet Report columns A, and, B:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">DCN</td><td style=";">ROFO</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">EME</td><td style=";">ROFO</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">HOU</td><td style=";">ROFR</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Report</p><br /><br />



Code:
Sub ReorgData()
' hiker95, 09/09/2014, ME804334
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, n As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Reference")
If Not Evaluate("ISREF(Report!A1)") Then Worksheets.Add(After:=w1).Name = "Report"
Set wr = Sheets("Report")
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  a = .Range("A1:B" & lr)
  n = Application.CountA(.Range("B1:B" & lr))
  ReDim o(1 To n, 1 To 2)
End With
For i = 1 To lr
  If a(i, 2) <> "" Then
    j = j + 1
    o(j, 1) = a(i, 1)
    o(j, 2) = a(i, 2)
  End If
Next i
With wr
  .Columns(1).Resize(, 2).ClearContents
  .Range("A1").Resize(n, 2).Value = o
  .Columns(1).Resize(, 2).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Last edited:

johnmpl

Board Regular
Joined
Jun 14, 2013
Messages
235
Hi!

This two macros made the same...

Code:
Sub ReOrg2()
    Dim f&, c&
    Application.ScreenUpdating = 0
    With Sheet1.Range("A1").CurrentRegion
        f = .Rows.Count
        c = .Columns.Count
    End With
    With Sheet2
        .Range("A1").Resize(f, c) = Sheet1.Range("A1").CurrentRegion.Value
        .Range("B1").Resize(f).SpecialCells(4).EntireRow.Delete
    End With
    Application.ScreenUpdating = 1
End Sub

Or this:

Code:
Sub ReOrg3()    
    Dim uf&
    Application.ScreenUpdating = 0
    Sheet1.Range("A1").CurrentRegion.Copy Sheet2.Range("A1")
    uf = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    Sheet2.Range("B1:B" & uf).SpecialCells(4).EntireRow.Delete
    Application.ScreenUpdating = 1
End Sub

Sheet1 and Sheet2 are the internal names of the sheets. Sheet1 is the reference sheet and Sheet2 is the report sheet. You can change agree what you need.
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
jimtheeagle,

You are very welcome. Glad we could help.

And, come back anytime.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,987
Members
409,614
Latest member
wile2u

This Week's Hot Topics

Top