Tidy a list

[MarkAndrews]

I have the following list which i would like to just show the names

Is there an easy way to do this other than using Text To Columns?

thanks

Generic Exchanges.xls
	E	F	G	H
2	AlyaYasmin(S&PFE-BRD)
3	BarbaraVickers(S&PFE-BRD)
4	BenKelly(S&PFE-BRD)
5	CaroleDishman(S&PFE-BRD
6	CavelNichols(S&PFE-BRD)
7	DamianHarrison(S&PFE-BRD
8	DanielleCruise(S&PFE-MAN)
9	DaoudHussain(S&PFE-BRD)
10	EstherYoung(S&PFE-MAN)
11	GwilymDavies(S&PFE-MAN)
12	HelenHorner(S&PFE-BRDH/
13	HelenManning(S&PFE-BRD)
14	HelenaReeve(S&PFE-BRD)
15	JamieCruise(S&PFE-MAN)
16	JaniceCopley(S&PFE-BRDH
Workings

 

[MarkAndrews]

Hi Mark

Is there a reason for this?

Yes, there is. The formula is in C2 and looks at A3 expecting to find a "(" in there somewehere. A3 contains Andrew Arnold - no brackets, hence the error.

HTH

DominicB

Gotcha! Realised that after i'd posted

Thanks mate

Hope you are well

 

[MarkAndrews]

Problem mate - Why is this name wrong (brackets are still shown)

Generic Exchanges.xls
	D	E	F	G
5	BenKelly(S&PFE-BRD)	BenKelly(S&P
Completion Results

 

[NBVC]

You're mixing cell references.

Should be:

=LEFT(D5,SEARCH("(",D5)-2)

 

[MarkAndrews]

You're mixing cell references.

Should be:

=LEFT(D5,SEARCH("(",D5)-2)

Is there anyway to make this generic??

I have about 30 rows, with names followed by bracketed words

??

 

[NBVC]

What exactly do you mean by "generic"?

Copying down the formula, updates it to match the corresponding row.

 

[MarkAndrews]

Your right, NBVC

I was mixing cell references, how i didn't notice this i'll never know (i am having a very bad week so far)

Thanks for your help as ever

 

[MarkAndrews]

How would i amend this in my code?

I am still learning with VBA

ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],SEARCH(""("",R[1]C[-4])-2)"

Sorted, please ignore (I cheated and used the recorder)

 ActiveCell.FormulaR1C1 = "=LEFT(RC[-4],SEARCH(""("",RC[-4])-1)"

 

[MarkAndrews]

Hi Mark

Aaaaaahhh, got you!

=CONCATENATE(F2,"(" &E2,$H$1&")")

HTH

DominicB

Dominic, following on from this

This is my current table

Generic Exchanges.xls
	A	B	C	D	E	F	G	H
1	FeeEarner	Freehold	Leasehold	Freehold	Leasehold	TotalExchanges	Merge	LH
2	AlyaYasmin(S&PFE-BRD)	AlyaYasmin(S&PFE-BRD)Freehold	AlyaYasmin(S&PFE-BRD)Leasehold	19	1	20	20(1LH)
3	BarbaraVickers(S&PFE-BRD)	BarbaraVickers(S&PFE-BRD)Freehold	BarbaraVickers(S&PFE-BRD)Leasehold	17	1	18	18(1LH)
Exchanges Results

D = Freehold
E = Leasehold

What i need to add in is a column for Shared Ownership between E & F

I need the total to appear in G but as

20 (2LH 16FH 2SO) so i need to further concatenate

This is my current macro, which i'll need to amend (God knows how)

Sub Exchanges()

    Application.ScreenUpdating = False
    Sheets("Paste Completions Report Here").Visible = True
    Sheets("Paste Exchanges Report Here").Visible = True
    Sheets("Completion Results").Visible = True
    Sheets("Exchanges Results").Visible = True
    MsgBox ("Calculating Exchange Figures ")
    Sheets("Paste Exchanges Report Here").Select
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Fee Earner & Category"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-1]"
    Range("K2").Select
    Columns("K:K").EntireColumn.AutoFit
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Columns("K:K").EntireColumn.AutoFit
    ActiveWindow.SmallScroll ToRight:=6
    Range("M12").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Rows("1:1").Select
    Selection.AutoFilter
    Columns("D:D").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( _
        "D:D"), CopyToRange:=Range("Y1"), Unique:=True
    ActiveWindow.SmallScroll ToRight:=12
    Columns("Y:Y").EntireColumn.AutoFit
    Columns("Y:Y").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Cut
    Sheets("Exchanges Results").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]&R1C2"
    Range("B2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Sheets("Exchanges Results").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&R1C3"
    Range("C2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Sheets("Exchanges Results").Select
    Columns("A:D").Select
    Selection.EntireColumn.Hidden = False
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF('Paste Exchanges Report Here'!C[7],'Exchanges Results'!RC[-2])"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF('Paste Exchanges Report Here'!C[6],'Exchanges Results'!RC[-2])"
    Range("D2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Sheets("exchanges results").Select
    Range("E2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Sheets("Exchanges Results").Select
    Columns("B:C").Select
    Range("C1").Activate
    Selection.EntireColumn.Hidden = True
    Sheets("Exchanges Results").Select
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
    Range("F2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    Range("a1").Select
    Sheets("Exchanges Results").Select
    Range("G2").Select
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=0=TRUE,RC[-1],CONCATENATE(RC[-1],""(""&RC[-2],R1C8&"")""))"
    Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
    ActiveWindow.SmallScroll Down:=-20
    Range("A1").Select
    Sheets("Exchanges Results").Select
    Columns("A:A").Select
    Selection.Copy
    Columns("N:N").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("P:P").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Columns("N:N").EntireColumn.AutoFit
    Columns("P:P").EntireColumn.AutoFit
    Range("O2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Workings!C[-14]:C[-13],2,FALSE)"
    Range("O3").Select
    Columns("O:O").EntireColumn.AutoFit
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O32")
    Range("O2:O32").Select
    Columns("N:N").Select
    Selection.EntireColumn.Hidden = True
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Fee Earner"
    Range("O2").Select
    Columns("O:O").EntireColumn.AutoFit
    Columns("P:P").EntireColumn.AutoFit
    Columns("O:P").Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Columns("P:P").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("A1").Select
    Sheets("Paste Completions Report Here").Visible = False
    Sheets("Paste Exchanges Report Here").Visible = False
    Sheets("Completion Results").Visible = False
    Sheets("Exchanges Results").Visible = False
    Application.ScreenUpdating = True
End Sub

 

[DominicB]

Hi Mark

OK, one step at a time, we'll worry about the macro later. We'll try and get the formula right first, but you're missing some critical info :

What i need to add in is a column for Shared Ownership between E & F

What denotes shared ownership? Where is it? Do you need to count instances of shared ownership?

I need the total to appear in G

The total number of shared ownerships?

20 (2LH 16FH 2SO) so i need to further concatenate

What does this mean?

DominicB

 

[MarkAndrews]

Hi Mate, thanks for picking this up again

To answer your questions

1 - From the HTML image above

LH = Leashold (D1, count)
FH = Freehold (E1 count)

I need to add in another column for Shard ownership between C & D

2 - from the counts in column B & C & D (New columm D - for Shard Ownership)

The count of this will appear in column G

3 -

20 (2LH 16FH 2SO) - This is how the total in (currently) G will ned to appear

Basically so it'll show the total, then the breakdown of this total - so it shows as 20 (2LH 16FH 2SO)

Hope this makes sense?

Then the macro needs amending, moving the total column used at the moment, to read from the column additions i need to make

Thanks mate, really appreciate this