I have an excel sheet where the data is in the following format
id1 abc dghajsgdjh 123
id1 abc uidgiutgcui 245
id2 xyz hqwhdqwq 672
I need to extract only one row from first two rows and the third row. I need only two rows with id1 and id2.
Can anybody help me how to get this output from a large excel sheet?
Try this array formula in G2 and pull formula to the right and then down.
=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$20=$F2,ROW($A$2:$A$20)),1)),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
id1 abc dghajsgdjh id1 abc dghajsgdjh id1 abc uidgiutgcui id2 xyz hqwhdqwq id2 xyz hqwhdqwq
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks !!
The file is too large, it has more than 5000 rows and 20 columns. I want to extract those rows and save it in another worksheet. I think that I can write a macro, but I have no idea how to write a macro.
It is not working !!
This should work for you. Adjust the cell ranges to suit your actual data.
In F2 enter this array formula (enter with Ctrl + Shift + Enter) and copy down.
Formula:
Please Login or Register to view this content.
In G2 enter this formula and copy across and down.
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks