Using formula how to listing index from 2 sheets to another sheet sorted no duplication

[Dackson]

Dear Sir,

Using formula how to listing dynamic index from 2 sheets to another sheet will sorted and no duplication...

Please help me............

 

[Luke M]

Can we limit things down a bit, purely for calculation sake? If you truly say "anywhere" in the sheet, that could mean we need our formula to search every cell. That gets us well into over a billion cells just on one sheet!
Further, do all ID's of interest start with a "N", or do we just need to look under certain column headers? Any chance we could limit this down to 1 sheet, or do we truly need to look at 2 (or more??) sheets?

 

[Dackson]

Dear Sir,
"anywhere" means Under of 'ID CODE#' only in the sheets not in anywhere from the excel all cells. But my file actually total 6 Sheets but me showed 2 sheets only that mean sheet will be more than one.

regads,

 

[Luke M]

That helps a little, as now we only have a few million rather than a few billion cells...
Is VB ok, or are we limited to strictly formulas only?

 

[Dackson]

Hi first try formula if you dont have anyway then pls use vb

 

[Luke M]

Try this. It uses the worksheet_activate event to automatically refresh the list. the ANSWER sheet is ignored by macro, but it looks at every other sheet. Depending on how big the lists is will directly influence how long macro takes, but with example, you don't even notice it.

 

[Dackson]

Hi,
Its fine sir..............
If i want to use this macro for any other sheet What i want to do.....

 

[Luke M]

The macro is written to search all sheets. If you look in the code:

For Each WS In ThisWorkbook.Worksheets
    If WS.Name <> "ANSWER" Then
'other stuff
'....
Next WS

This is where it loops through. If you don't want to loop through all the section, remove the For...Next lines, and you could do something like:

Set WS = Worksheets("Sheet1")

 

[Dackson]

Thanks sir....
Thanks very much...........