FORMULA NEEDED HELP

Kamal Subhani

Board Regular
Joined
Jan 26, 2004
Messages
146
Hi all this is my first post in the forum.


I have a blank sheet and I want to link it to muliple sheets more than 15.

Let say in Sheet1 ColumnB I have the following names :Tom , Jim
In the blank sheet I want Tom ,Jim to be displayed in cell B1 nd B2.

in Sheet2 ColumnB I have the following names :Tom,Marry, John(in B1,B2,B3)
in the Blank sheet in want Tom,Jim,Marry,John to be displayed (in B1,B2,B3,B4)

in Sheet3 Column B I have Kamal,Sharon in cell B1,B2

in BlankSheet I want Tom,Jim,Marry,John,Kamal,Sharaon in cell B1,B2,B3,B4,B5,B6

In actual a lot more names but there is no duplcate enrty in each sheet( names are repeted in different sheets but not in the same columns)

I want to summarize all the names in the Blankshhet Coumn B.

Is there any formula to achive the desired results?

Hope that you will understand my question and some Genius person will solve my problem.

Thanks
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Mat

Well-known Member
Joined
Sep 17, 2003
Messages
509
If I understand what you want, this should work, just replace YourSheetName by the good name, BlankSheet I guess, (This sheet must already exist before running the code)

Code:
Sub NameList()
Dim ws As Worksheet
Dim SheetName As String
Dim i As Double, j As Double

'Change YourSheetName by the name of the
'sheet where all the names will be
SheetName = "YourSheetName"

Sheets(SheetName).Select

'Loop to copy all names on the new sheet
For Each ws In Worksheets
    If ws.Name <> SheetName Then
        ws.Range(ws.Range("B1"), ws.Range("B65536").End(xlUp)).Copy
        
        If Sheets(SheetName).Range("B1").Value = "" Then
            Sheets(SheetName).Range("B1").Select
        Else
            Sheets(SheetName).Range("B65536").End(xlUp).Offset(1, 0).Select
        End If
        
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
Next

'Loop to erase the names that comes more than once
For i = 1 To Range("B65536").End(xlUp).Row
    For j = i + 1 To Range("B65536").End(xlUp).Row
        If UCase(Cells(i, 2).Value) = UCase(Cells(j, 2).Value) Then
            Cells(j, 2).EntireRow.Delete
            j = j - 1
        End If
    Next j
Next i
 

Range("B1").Select
   
    
End Sub

Hope This Helps,
 

rrdonutz

Well-known Member
Joined
Jan 15, 2003
Messages
564
Hi Kamal,

Welcome to the board!

Here is a non-VBA approach. If you install MOREFUNC.XLL (see the "Global Announcement: Recommended Add-ins and Links" topic on the first page of this forum), you can take advantage of its functionality. One possible formula to achieve what you want:

=UNIQUEVALUES(THREED(Sheet2:Sheet18!B1:B50))

which should be entered as an array formula over as many cells (simultaneously) as you think necessary to capture the number of unique items you expect. That is, if you expect 40 possible unique items, highlight a range of cells from B1 to B40 (at least) on your "Blanksheet" and enter the formula above (or an appropriate variation) with 'Ctrl+Shift+Enter'. To edit the formula, select a cell in this array range and press 'Ctrl' + the '/' keys together.

Note: to change the order of unique items to 'ascending', change the formula above to:

=UNIQUEVALUES(THREED(Sheet2:Sheet18!B1:B50),1)

--Tom
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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