Laying data out find the first value then the second etc.

[Carterabby]

I am trying to organize some data the way I want it. I am running into some issues. I thank you in advance for any help.

While its hard to explain in writing I think posting a workbook with some instructions will help.


In column c labelled VIS ( in red) You will see a team name . I am trying to organize a series of boxscores that match the vis&ptsscrd. When a boxscore matches vis&ptsscrd I want to move that data and the 11 columns to the right of the match over to visitors section columns de:dx ( yellow section) . Then If there is another match continue to do the same . Then when that is complete move to the home&ptsscrd.

I have provided a example column that I think explains this a little better.


Thank you so much for any help

[Limor_OP]

Hi
If I understand you correct than try this in DE2 and to the right for VIS1+VIS2:

=IFERROR(INDEX(J$1:J$570,AGGREGATE(15,6,ROW($A$2:$A$570)/(LEFT($J$2:$J$570,SEARCH("PTSSCRD",$J$2:$J$570)-1)=($C$2:$C$570)),ROWS($A$1:A1))),"")

[Carterabby]

Thank you for your reply.

It seems your code does retrieve data and will count the visptsscrd and pull info while counting its placement it fails to process the homeptsscrd. Those I want on the home section.

For example your code on the first line pulls the first two the way I want but put the code in the vis three area it pulls the homeptsscrd ( in this example "CLEPtsScrd" which I would want in the home section. vis 3 and vis 4 would be blank here as there is not a 3rd bosptscrrd or 4th , The data is somewhat uniform but really the 1st visptsscrd could be in the 6th boxscore or anyplace for that matter and why I am trying to organize it .
Thank you again for your help!

[Carterabby]

One idea I failed at was make another helper column which I failed at. Basically count the visptssccrd and put it beside to the left .

for example i2 would be 1bosptsscrd and v2 would be 2bosptsscrd.

Than I could vlookup all 8 columns and get this data sorted kinda the way I want. problem I had here was usually counting things I go down and not to the side and I was getting spill errors etcs..


I used a code like this to get items that only appear once in this example "3ptfgm"

IFS($C24&"3ptfgm"=$DZ24,DZ24,$C24&"3ptfgm"=$DN24,DN24,$C24&"3ptfgm"=$DB24,DB24,$C24&"3ptfgm"=$CP24,CP24,$C24&"3ptfgm"=$CD24,CD24,$C24&"3ptfgm"=$BR24,BR24,$C24&"3ptfgm"=$BF24,BF24,$C24&"3ptfgm"=$AT24,AT24,$C24&"3ptfgm"=$EL24,EL24,$C24&"3ptfgm"=$EX24,EX24,$C24&"3ptfgm"=$FJ24,FJ24)


This works but it cant find the second one as it only processes the first one it see's.

[Limor_OP]

OK I see where the problem is, will try to fix that...

[Carterabby]

your awesome thank you !

[Limor_OP]

Hi,

I added In line 1 VIS# and Home# to the proper column, + in DE:GU - it is needed for the formula to workproperly.

DE and across for all yellow region:

Please Login or Register  to view this content.

FA and across for the red region:

Please Login or Register  to view this content.

I think that your manually typed in results were wrong for Home1
as Lebron James is CLEPtsScrd and HOME is NWO


Hopefully this formulae will work for you this time....

BTW - It was really hard to work with such a big data file, next time just post a sample according to the instructions in the forum, given in the yellow banner at the top of the page....

[Carterabby]

Belinda Thank you for your time. Unfortunately it is not working.Ive tried with it over and over. I am going to take your suggesting and make a smaller data file and somehow make it more straightforward to understand

Again Thank you for all your time.

[Limor_OP]

Hi Carterabby
What do you mean doesnt work? I've attached the workbook , you just need to drag the formula to the blank columns (I didnt do it myself as the size of the file became larger than the allowed size in this forum)

[Limor_OP]

Here's with a smaller database
Let me know what doesnt work...

[Carterabby]

I think its a communication error on my part of what I am trying to do.



cell bf3 would be bosptsscrd .
cell ca3 would be bosptsscrd.
Cell ch3 would be blank as there is no more bosptsscrd in row 3.



df3 would be cleptsscrd
dr3 would be cleptsscrd


this is the home team.


vis and home is defined with Columns c and f


I am really sorry about all this.I am struggling to convey my objective here I guess.


Thanks you again.

[Limor_OP]

OK, trying again
I did have a shifting of the range , and it started from the 2nd match instead of the 1st.....