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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,713
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:


Excel 2007
AB
1DCN
2DCNROFO
3EMEROFO
4EME
5EME
6HOU
7HOUROFR
8
Reference


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


Excel 2007
AB
1DCNROFO
2EMEROFO
3HOUROFR
4
Report




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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,485
Messages
5,831,940
Members
430,096
Latest member
IBRAHEEM NAJJAR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top