• 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.

How to capture each word/number from a list of row which contain the same duplicate numbers / words

K

Kumar@raja

Member
Dear Friends,

Herewith attached an excel sheet containing Emp- ID and Location in sheet 1. Here in Column B consists of 163 Rows and all these rows filled with different locations and most of them are duplicates. Here, I would like capture each one from location. Hence, kindly let me know how we can do the same rather than using filter option by using a function/formula. I Hope you understand my query, hence kindly help me out.

Result should come as follows "-

VT-B'Hills
VT – BLR
QC – HYD
VT – ODC
QC – HYD
QC-BLR
QC-CHE

As you all know that we can count each location by using Count if formula, but I need to capture the each among the list location wise by kind of formula Count If or sum if .

Best Regards,
Kumar
 

Attachments

  • Work Book.xlsx
    11.1 KB · Views: 6
Hi Kumar,

Try below formula in G4 and copy down.

=INDEX($B$3:$B$165,SMALL(IF(FREQUENCY(MATCH($B$3:$B$165,$B$3:$B$165,0),MATCH($B$3:$B$165,$B$3:$B$165,0)),ROW($B$3:$B$165)-ROW($B$3)+1),ROWS(I$4:I4)))

Enter with Ctrl+Shift+Enter.

Regards,
 
Hi Somendra,

Thank you very much it is working........ It became a big puzzle how you have created a formula to get the result.
Really appreciated your command and proficiency on excel...............How we can write these kinds of formulas, seems big puzzle for me.

Further, I have a query regarding the selection of the data by using a key board shut cut key. As you are aware that we can copy the formula from particular row to below until we require the result by using a short key like Shift+Cont+Down arrow .Here question is, by using the same short cut the selection will go to until the last row of the sheet which ends row no. 1048576 but if I need to stop the cursor at row 250 or until the last row of the data, how can we stop. Is there any trick for the same, I often get the problem here in day to day work, hence kindly help me for the same.

Over again thanks in ton for your kind support.

Regards,
Kumar
 
Hi Kumar,

If the column where you want to populate formula have a neighboring column filled with data, and you double click on the cross hair of the cell where formula is, it will automatically populate the formula in all the cells below it till the cell where you have data in the next column.

Well I do not know any short cut to stop the copying or selection to say at 250th row. The best way I think of doing this, is to copy the formula, select the number of rows where you want to populate it and paste it.

Second method can be have of count of total such result, filled one column with sr# from 1 to count, put the formula in next cell and double click on the cross hair to get the formula populated automatically.

Regards,
 
You must log in or register to reply here.
Back
Top