Copy Paste Rows by Column Criteria

I'll be honest, I have no idea where to start as i only know the very basic formulas.

What i am looking to accomplish is getting Excel to copy entire rows of data to a new worksheet using only the data in column CE.

I have a list of criteria for Excel to find in column CE, around 585 different ids in this case, as Excel finds each of the ids it will copy each
entire row it finds a match for into the new worksheet into individual rows exactly the way it found it in the workbook it is copying from.

Can anyone help me ?

I am not sure if what I am trying to do is specific enough, it is the best way i can explain it.

Thanks in advance.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

I'd probably go with something along the lines of "Copy Paste Rows by Column Criteria" or some such.

A short VB Loop or Small Index array could do it, but I'd attach a work example so that our advice can be specific to your need.

Hi, welcome to the forum

Thanks for the title change. I understand that your files are large, but many members are unable (or unwilling) to access file-hosting site (company fire-walls and stuff). can you put together a small mock-up of what you have and what you want, andthen upload that workbook here?

This, copied down and across should give you what you want. I sugge3st that you add the headings from Players, so you know which is which...
=IFERROR(INDEX(players.xlsx!A$2:A$30,MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),"")

Yes, just copy/past. I am not using the headings in the formula (although I could have done, if you want them in a different sequence)

Originally Posted by FDibbins

Yes, just copy/past. I am not using the headings in the formula (although I could have done, if you want them in a different sequence)

I would only want them to be copied one time.

I must ask, where must I paste this formula, in sheet 1 of the players workbook ?
Is there a specific way i need to open the workbooks or steps I need to take ?

Also, in the formula, =IFERROR(INDEX(players.xlsx!A$2:A$30,MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),""), the numbers in bold are the last
row, correct ?

Would I need to change that to say 154777, if 154777 is my last row ?

Put that formula in B2 of Player ID Ref WB, and copy down as far as you need it, and as far across as you have headings. The Players WB does not even need to be open

Originally Posted by FDibbins

Put that formula in B2 of Player ID Ref WB, and copy down as far as you need it, and as far across as you have headings. The Players WB does not even need to be open

That is incredible, damn you are good.

Thanks for the formula, it's genius and a hell of a time saver.

Thread Solved.

Thanks for the kind words, always appreciated

From your PM to me...

works perfectly for the players workbook.

I tried to alter it to use on other workbooks using the same basic layout as the players workbook.

In this case teamplayerlinks, using the above formula, I subbed some data out with different data but it yields me no results.

ex. =IFERROR(INDEX(teamplayerlinks.xlsx!A$2:A$X,MATCH($A2,teamplayerlinks.xlsx!$H$2:$H$X,0)),"")

X being the amount of rows, 30 in the sample workbook I attached in my post and H being basically the same as CE
in the players workbook.

I was hoping it would pull all the data for each row of data relating to the referenced ID in column H, in this case anywhere from 15 to 42 rows
of data.

Can you explain to me why I do not get the same results, it almost seems as the players formula is for pulling a single row of data
for each ID referenced.

What do I need to do for the formula to, for example find this ID in column H and everything in the column with that ID gets pulled like it does in the players workbook ?

You will notice that in my formula, the INDEX part references a single column range (bolded)...
=IFERROR(INDEX(players.xlsx!A$2:A$30,MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),""),MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),"")
while your INDEX references a range of columns...
=IFERROR(INDEX(teamplayerlinks.xlsx!A$2:A$X,MATCH($A2,teamplayerlinks.xlsx!$H$2:$H$X,0)),"")

The syntax for INDEX() is =INDEX(range,row,column)

INDEX with a single MATCH needs only a single column range ref...this makes it easy to copy across and pull in data from consecutive columns, as you dont need to tell it which column to use.

If you are using multiple columns in the range, you need to tell INDEX which column you want to pull from, so you need to bring another function (usually another MATCH() ) to ID the column you want to use.

So you end up with...
=INDEX(single-column-range,row-to-use)...it will pull from the column specified in the INDEX
or
=INDEX(multiple-column-range,row-to-use,column-to-use)...it will pull from the column specified in the column specified.

To find either the row number or the column number, you generally use the MATCH() function

Originally Posted by FDibbins

From your PM to me...


You will notice that in my formula, the INDEX part references a single column range (bolded)...
=IFERROR(INDEX(players.xlsx!A$2:A$30,MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),""),MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),"")
while your INDEX references a range of columns...
=IFERROR(INDEX(teamplayerlinks.xlsx!A$2:A$X,MATCH($A2,teamplayerlinks.xlsx!$H$2:$H$X,0)),"")

The syntax for INDEX() is =INDEX(range,row,column)

INDEX with a single MATCH needs only a single column range ref...this makes it easy to copy across and pull in data from consecutive columns, as you dont need to tell it which column to use.

If you are using multiple columns in the range, you need to tell INDEX which column you want to pull from, so you need to bring another function (usually another MATCH() ) to ID the column you want to use.

So you end up with...
=INDEX(single-column-range,row-to-use)...it will pull from the column specified in the INDEX
or
=INDEX(multiple-column-range,row-to-use,column-to-use)...it will pull from the column specified in the column specified.

To find either the row number or the column number, you generally use the MATCH() function

I have to admit I am a bit lost here, not sure I see the difference between what you had done and what I had done.

All I had done was change players.xlsx to teamplayerlinks.xlsx, the CE to H and 30 to the last row numeral which happened to be 17554.

I think I may require an Excel for Dummies type demonstration.

If I tell excel which column to pull from, which I had thought I had done by changing CE to H would make it do what you had the formula do.

I need to figure out how to get Excel to look into another workbook to a certain column and only that column for it's MATCH but pull more than a single row if they exist
within that workbook.

I almost feel like I need to upload another example but don't want to take up too much of your time.

oops reading again, I see that your INDEX range is indeed a single column range.

I just noticed that your Players upload has only 1 sheet, so the formula I put together did not need a sheet name in it (because there is only 1 sheet in that file). But if you have more than 1 sheet, that formula will have a problem. Use this instead...
=IFERROR(INDEX([players.xlsx]players!A$2:A$30,MATCH($A2,[players.xlsx]players!$CE$2:$CE$30,0)),"")

but pull more than a single row if they exist

That part will not work with my formula, I will need to adjust it a bit. For that, can I ask that you upload another sample WB/s with samples of those too plz?

Originally Posted by FDibbins

oops reading again, I see that your INDEX range is indeed a single column range.

I just noticed that your Players upload has only 1 sheet, so the formula I put together did not need a sheet name in it (because there is only 1 sheet in that file). But if you have more than 1 sheet, that formula will have a problem. Use this instead...
=IFERROR(INDEX([players.xlsx]players!A$2:A$30,MATCH($A2,[players.xlsx]players!$CE$2:$CE$30,0)),"")


That part will not work with my formula, I will need to adjust it a bit. For that, can I ask that you upload another sample WB/s with samples of those too plz?

I have attached three workbooks, one is Team ID Reference workbook (same as Player ID Reference workbook ), teams workbook and teamplayerlinks workbook.

Using the Team ID Reference, I need a formula that will pull more than one row when it finds a match in the other workbooks using more than one ID to match in the
Team ID Reference workbook in only one column in the other two workbooks.

For the teams workbook it must find the match in column AP and the teamplayerlinks workbook from column H.

1,2,3,4,5,6,7,8,9,10,11,12 for example would be the team ids, the teams workbook will have a single row per ID and teamplayerlinks workbook will pull more than one
row per id, anywhere from 18 to 42 rows.

So I am in need of formulas to pull a single row from an ID like the formula for the players workbook and a formula for what I have explained above.

Thanks again.

Team ID Reference workbook contains a 1 in cell A1, and nothing else. I will see if I can use the Player ID WB instead

edit: Can you give me a few sample answers to work on please?

Reexamining your post i realized what you meant by 1, it was a mistake, if you use the attachments in my previous post you will have what you need.

You can use the Team ID Reference sheet from the earlier posts.

OK, I hate to admit it, but I have got lost in this 1 It has carried on for a while now, andthings have changedm and I have lost track of what we were doing. I looked at the playerID in those files and cant find any match now.

Can I ask that we start again, from the beginning (on this same thread).

Explain again (if you would), exactly what you have and what you want. Upload the files again, and show a few samples of what you want. Sorry for the confusion

OK, I *think* im with you now.

The players workbook will search the list in the Player ID Reference workbook and return an entire row per ID columns A-CY. The formula must search the CE column of the players workbook.

My original formula, copied down and across, will do this...
=IFERROR(INDEX(players.xlsx!A$2:A$30,MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),"")

The teams workbook is basically the same except it must use the Team ID Reference and return an entire row per each ID columns A-BN. The formula must search the AP column of the teams workbook.

Try this 1, copied down and across...
=IFERROR(INDEX(teams.xlsx!A$2:A$30,MATCH($A2,teams.xlsx!$AP$2:$AP$30,0)),"")

You said 2 formulas, so I guess you count these as the same?

I think my initial problem was that I did not realise that you had 5 different files, I thought there were 3 or 3 lol

Now, for your 3rd part..."The teamplayerlinks workbook will..."
Is the data in the Links wb what you want the answers to look like, or is that also data you have downloaded? I have tried to match up teh various headings, and apart from "overallrating", none seem to match.

So, if we have those 1st 2 sorted out, can you walk me though how ou would do your 3rd part manually? (sorry to be thick on this 1)

Originally Posted by FDibbins

OK, I *think* im with you now.


My original formula, copied down and across, will do this...
=IFERROR(INDEX(players.xlsx!A$2:A$30,MATCH($A2,players.xlsx!$CE$2:$CE$30,0)),"")


Try this 1, copied down and across...
=IFERROR(INDEX(teams.xlsx!A$2:A$30,MATCH($A2,teams.xlsx!$AP$2:$AP$30,0)),"")

You said 2 formulas, so I guess you count these as the same?

I think my initial problem was that I did not realise that you had 5 different files, I thought there were 3 or 3 lol

Now, for your 3rd part..."The teamplayerlinks workbook will..."
Is the data in the Links wb what you want the answers to look like, or is that also data you have downloaded? I have tried to match up teh various headings, and apart from "overallrating", none seem to match.

So, if we have those 1st 2 sorted out, can you walk me though how ou would do your 3rd part manually? (sorry to be thick on this 1)

I have 13 different workbooks to work with, I hope the 2 formulas will work for them all.

Basically what I am looking at is using the players formula for the teams, formations, etc, all returning a single row of data per team ID.

The teamplayerlinks will return multiple rows per team ID, in the workbooks I attached you see the headers in which the data falls under, I would
like the same returns as the single rows of data but multiple rows instead.

If you look at the teamplayerlinks workbook under column H you will see the same value multiple times for each ID, when the formula finds that ID
it will return rows of data per each ID starting from column A to column O.

Your players formula returns all the data across the entire row once it finds the ID in column CE, I would like all the formulas to do the same.

Hopefully the single return formula you created will work for all the workbooks needing a single row of data by simply changing the column and
amount of rows to match each of the other formulas.

I can verify that your original players formula does in fact work flawlessly, I haven't tried the column and amount of rows swap yet to see if it would work
with the teams workbook.

I don't see why it wouldn't work considering they are basically the same formula.

If you base the extract on the TeamID in TeamPlayerLinks column H, using this (looking in to Teams)...
=INDEX(teams.xlsx!A$2:A$21,MATCH($H2,teams.xlsx!AP$2:AP$21,0))

This will produce teh exact same results for every row with the same TeamID. Is this what you want??

Originally Posted by FDibbins

If you base the extract on the TeamID in TeamPlayerLinks column H, using this (looking in to Teams)...
=INDEX(teams.xlsx!A$2:A$21,MATCH($H2,teams.xlsx!AP$2:AP$21,0))

This will produce teh exact same results for every row with the same TeamID. Is this what you want??

Not sure what you mean ?

Using the teams workbook or the Team ID Reference workbook ?

I would prefer the Team ID Reference workbook as the list used to search rather than the teams workbook.

When I use the players/teams formula I enter the formula in cell B2 of either reference workbook I get exactly what I want.

I think it would be harder using the teams workbook seeing there is already so much data in the workbook and the fact that
the teamplayerlinks workbook I sent you is only some of the teams in the full workbook, if i were to use the full workbook I
would be getting data I may not be looking for.

Sorry for the delay, this thread got pushed way down, and I did not see it

I will take another look at it for you