array?

[jimtheeagle]

Hi

I have a reference tab that looks like this:

DCN
DCN	ROFO
EME	ROFO
EME
EME
HOU
HOU	ROFR

<tbody>
</tbody>

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

DCN	ROFO
EME	ROFO
HOU	ROFR

<tbody>
</tbody>

Is there a simple way to do this?

Thanks

 

[steve the fish]

Can you sort reference tab by column B? Then a vlookup works.

 

[jimtheeagle]

Steve

There are actually other columns I'm nt showing so a sort wont work.

 

[Weazel]

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]

jimtheeagle,

You did state that you had two worksheets.

How about a macro solution?

Sample raw data in worksheet Reference:

Excel 2007
	A	B
1	DCN
2	DCN	ROFO
3	EME	ROFO
4	EME
5	EME
6	HOU
7	HOU	ROFR
8
Reference

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

Excel 2007
	A	B
1	DCN	ROFO
2	EME	ROFO
3	HOU	ROFR
4
Report

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.

 

[johnmpl]

Hi!

This two macros made the same...

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:

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.

 

[jimtheeagle]

Thank you everybody

 

[hiker95]

jimtheeagle,

You are very welcome. Glad we could help.

And, come back anytime.