• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

extracting value from group and inserting into another worksheet

VDS

Member
Dear All,

I attach herewith two worksheets. Sheet 1 gives list of items like Apple, Mango, Banana, Wheat, etc. and there is no limit to their total number. In the second worksheet, only one item to be sorted from 1st worksheet. I want the items Apple, Mango, Banana, to be extracted with formula. (each individual item only once). How it can be done ?

VDS
 

Attachments

  • DATA 1234.xls
    26.5 KB · Views: 9
@Narayan Sir

I downloded a file (2003 format) from the website. But, after saving and applying command, =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)), it displays # N/A. message. In the sample file, the data is not in order In my case, repeated number of words are in order.
the command
=INDEX($A$1:$A$5,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0),0)) gives "0" value.

Sir, could you pls rectify ?

VDS
 
Hi VDS,

On Sheet2 in A1, use below formula and copy down.

=INDEX(Sheet1!$A$1:$A$26,SMALL(IF(FREQUENCY(MATCH(Sheet1!$A$1:$A$26,Sheet1!$A$1:$A$26,0),MATCH(Sheet1!$A$1:$A$26,Sheet1!$A$1:$A$26,0)),ROW(Sheet1!$A$1:$A$26)-ROW(Sheet1!$A$1)+1),ROWS(Sheet2!A$1:A1)))

Note this is an array formula, so must be entered with Ctrl+Shift+Enter.

Regards,
 
Hi ,

The formula given on the page whose link I posted , relies on the fact that it is entered in any cell in row 2 ; therefore it assumes that your data also starts from row 2. If your data starts from row 1 , it will not work.

What it does is it sees if any item from the main list ( which has duplicates ) has occurred before ( the CUNTIF function does this ) ; if it has not , the COUNTIF function will have a 0 in its array of values , and the MATCH function will return the first position where there is a 0. The INDEX function then returns the value at this position from the main list.

The formula uses an array as the second parameter to the COUNTIF function , and needs to be entered as an array formula , using CTRL SHIFT ENTER.

This is a standard technique to return a list which does not contain duplicates from one which does. However , if you want a list which is also in sorted order , the same website has other pages devoted to this.

http://www.get-digital-help.com/200...nct-list-sorted-from-a-z-from-range-in-excel/

Narayan
 
Hello,

You might wish to try this.

Code:
Sub Extract_Unique()
Dim l As Long, dictionary As Object, i As Integer
Application.ScreenUpdating = False

l = Sheet2.Range("A" & Rows.Count).End(xlUp).Row 'Change the range & sheet as required

Set dictionary = CreateObject("scripting.dictionary")
Sheet1.Columns(1).Cells.Clear 'Clear the contents from col
  
    On Error Resume Next
        For i = 1 To l
            If Len(Sheet2.Cells(i, 1)) <> "" Then 'find unique in col A
                dictionary.Add Sheet2.Cells(i, 1).Value, 1
            End If
    Next
    'send uniques to col A
    Sheet1.Cells(1, 1).Resize(dictionary.Count).Value = _
        Application.Transpose(dictionary.keys)
    dictionary.RemoveAll
    Set dictionary = Nothing
Application.ScreenUpdating = True
End Sub
 
Hello,
:
Code:
    On Error Resume Next
        For i = 1 To l
            If Len(Sheet2.Cells(i, 1)) <> "" Then 'find unique in col A
                dictionary.Add Sheet2.Cells(i, 1).Value, 1
            End If
    Next
One small suggestion in your code.

Unlike collection object, dictionary stores indexed list and so you can use a method .exists to check if the entry is there in dictionary like:
Code:
  'On Error Resume Next
  For i = 1 To l
  If Len(Sheet2.Cells(i, 1).Value) <> "" And _
  Not dictionary.exists(Sheet2.Cells(i, 1).Value) Then 'find unique in col A
  dictionary.Add Sheet2.Cells(i, 1).Value, 1
  End If
  Next

The other issue with On Error Resume Next is that it might mask some other error somewhere else.
 
In addition to Shrivallabha suggestion you might interested to try this...
It will also quite fast to extracting unique from thousands of row in-respect of any formula.


Code:
Sub Extract_Unique_List()
Dim l As Long, dictionary As Object, i As Integer, rng As Range
Application.ScreenUpdating = False

l = Sheet2.Range("A" & Rows.Count).End(xlUp).Row 'Change the range & sheet as required

Set dictionary = CreateObject("scripting.dictionary")
Sheet1.Columns(1).Cells.Clear 'Clear the contents from col
   
        For i = 1 To l
        Set rng = Sheet2.Cells(i, 1) 'Find unique in col A on sheet2/Change as required
          If Len(rng.Value) <> "" And _
              Not dictionary.exists(rng.Value) Then _
                  dictionary.Add rng.Value, 1
        Next
    'send uniques to col A on Sheet1
    Sheet1.Cells(1, 1).Resize(dictionary.Count).Value = _
        Application.Transpose(dictionary.keys)
    dictionary.RemoveAll
Set dictionary = Nothing
Application.ScreenUpdating = True
End Sub
 
@Somendra

Your formula is just like a magic. After testing in the live data It works splendid. But here are small issues :

a) When I am inserting data after sorting, this function works from alphabet A to T. If new party is entered starting with U to Z it is not sorted in the second sheet. The file is attached. Actually, the same data has already posted by one of my colleagues and issue was different. Is my range selected is incorrect ?

b) When any value is Zero and want display "-" we have to apply custom formatting and "-" at the appropriate places every time.

How this can be corrected ?

VDS
 
Sorry dear brother. File was missing


VDS
 

Attachments

  • Bank Guarantee_15-04-2014_00000000000_1.xls
    52.5 KB · Views: 2
Hi VDS,

Yes you have to adjust the range in your formula as suggested below:
1. Where ever you are using J4 in the ranges, change it to start from J5.
2. For getting values from U - Z you have to extend the from down further more.

For displaying the 0 values as "-" use custom number format as ##,00;;"-";

But I will suggest you for getting this type of summary try using pivot table (See below pic & attached file Sheet1).
Capture.JPG

I had inserted 4 columns in your main data to convert values in lacs and than used those columns for generating pivot, this is fast dynamic and worry free from extending the range.

Regards,
 

Attachments

  • Bank Guarantee_15-04-2014_00000000000_1.xls
    69.5 KB · Views: 4
@Somendra,

Thanks for your reply.

1. In the final report, I dont want the "SUM OF" to be repeated in 4 columns. How to do it.
2. Secondly,I dont want to change the format as this is the approved format. Is extra columns can be hidden ?



VDS
 
@Somendra,

I have applied the function " "0\.00, " ";-" "0\.00, " ". (as pointed out by Narayan sir) in the worksheet BG short in lacs in custom and reduced the command line of =Round.

This also works nice.

A small doubt. Both functions are same. Is there any difference ?


VDS
 
@VDS

Correct me If I am wrong, the suggestion of @NARAYANK991 Sir, what I think, is to do with Custom Number Format, where as Round is a function, which will bring down your value.

Say you enter 100000 in A1, so custom number format will show in cell 1, but the actual value will remain 100000 only. Where as ROUND function will bring it to 1 as a value. SO this will create same visual effect but may effect dependent maths .

Regards,
 
Back
Top