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 change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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>
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,218,899
Messages
6,145,090
Members
450,590
Latest member
Naneng

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
Back
Top