Show number that appears in multiple sheet

rancidian

New Member
Joined
May 20, 2011
Messages
4
Hello

My company monitors a block of flats and has various types of meter data reports on different sheets. I've pulled these all together into 1 workbook, but I now want to show on a summary sheet only flats that appear on more than one of the report tabs. Is there any way to do this?

Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Welcome to the board.

Short answer: Yes, but you're much more likely to get a more meaningful answer if you provide more detail/clarity, i.e. how is the data laid out, what columns/cells are your flats in? Have a look at Excel Jeanie - it's a useful tool if you want to post pictures of your worksheet into a question
 
Upvote 0
Sorry, can't use jeanie as our IT restricts download of software etc

There are 5 sheets. In each sheet, column A contains the flat number, and column B contains the block (i.e West block or East Block). All the other data is to the right of these columns, but I think they are irrelevant because I just want to identify the flats themselves and show which appear on more than one sheet, rather than the actual report data itself.

Also, each report does not contain every flat in the block, but only the flats that have some kind of problem with them, therefore, a certain flat will not always be in the same row in different sheets. Thought I'd mention this in case your solution needed the data in Column A and B to be exactly the same on each sheet

The version I'm using in 2003 if this matters
 
Upvote 0
This solution assumes that on each sheet a flat only appears once (but as you want, can occur on other sheets)

Insert a sheet at the front and call this "Summary" then run this macro:
Code:
Sub TwelveLordsLeaping()
 
Dim i As Long
 
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
 
With Sheets(1)
    .Select
    If .AutoFilterMode Then .AutoFilterMode = False
    .Cells.ClearContents
    .Range("A1") = "Identified Flats"
End With
 
For i = 2 To Worksheets.Count
    Sheets(i).Range("A1:A" & Sheets(i).Range("A" & Rows.Count).End(xlUp).row).Copy
    Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
Next i
 
With Sheets(1)
    i = Range("A" & Rows.Count).End(xlUp).row
    With .Range("B2")
        .Formula = "=COUNTIF($A$2:$A$" & i & ",A2)"
        .AutoFill Destination:=Range("B2:B" & i)
    End With
    .Calculate
    With .Range("B2:B" & i)
        .Copy
        .PasteSpecial Paste:=xlValues
    End With
    With .Range("A1:B" & i)
        .Sort Key1:=.Range("A1"), header:=xlYes
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="<2"
        .Offset(1, 0).Resize(.Rows.Count - 1).Rows.Delete
    End With
    .AutoFilterMode = False
End With
 
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
 
End Sub
 
Upvote 0
Sorry, forgot some additional code (added in red):
Rich (BB code):
Sub TwelveLordsLeaping()
 
Dim i As Long
 
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
 
With Sheets(1)
.Select
If .AutoFilterMode Then .AutoFilterMode = False
.ShowAllData
.Cells.ClearContents
.Range("A1") = "Identified Flats"
End With
 
For i = 2 To Worksheets.Count
Sheets(i).Range("A1:A" & Sheets(i).Range("A" & Rows.Count).End(xlUp).row).Copy
Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
Next i
 
With Sheets(1)
i = Range("A" & Rows.Count).End(xlUp).row
With .Range("B2")
.Formula = "=COUNTIF($A$2:$A$" & i & ",A2)"
.AutoFill Destination:=Range("B2:B" & i)
End With
.Calculate
With .Range("B2:B" & i)
.Copy
.PasteSpecial Paste:=xlValues
End With
With .Range("A1:B" & i)
.Sort Key1:=.Range("A1"), header:=xlYes
.AutoFilter
.AutoFilter Field:=2, Criteria1:="<2"
.Offset(1, 0).Resize(.Rows.Count - 1).Rows.Delete
End With
.AutoFilterMode = False
.Range("B2:B" & i).ClearContents
.Range("A1:A" & i).AdvancedFilter Action:=xlFilterInPlace, unique:=True
.Range("A1").Select
End With
 
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
 
End Sub
 
Last edited:
Upvote 0
I'm really sorry, but my understanding of visual basic is terrible. I assume I'm supposed to alter it slightly with the names of my tabs etc? If so here is some more detail

Tab1 - "Exeception report". flats in column A & B, row 4-42
Tab2 - "Exception report - Resolved Mtr issues. flats in column A & B, row 4-20
Tab3 - "High Consumption". flats in column A & B, row 4-18 and 22-36
Tab4 - "Meter faults". flats in column A & B, row 2-913
Tab5 - "Missing Reads". flats in column A & B, row 7-32

Is there any way of me sending the file to you so you can see exactly what I'm dealing with?

Again, Apologies!!
 
Upvote 0
My code eliminates the need to know what the tab names are, however, tab1 should be your summary page (then tab2 becomes "Exception Report" etc.

Didn't realise you meant columns A and B though - private message me your email address, I'll contact you and have a look
 
Upvote 0
ok, I've private mailed my email to you. I did add the summary sheet at the front as you instructed though, but said something like "compile eror, syntax error"
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,728
Members
452,939
Latest member
WCrawford

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