Extract related data from a second column from repeated entries in a first column

Hi,
I have a problem I can't seem to solve with any of the formulas I know and I can't find any info on google either, so any help is appreciated :-)

because it is hard to explain I will try to explain with screenshots:

I have a data sheet where the first column has repeated values, but each repeated value has different data associated with it in different columns (see first screenshot)
excel1.jpg

but I need to have the data in the first column only once (not repeated) and all the unique data associated with it spread out in one row (see screenshot 2).

excel2.jpg

I've tried many things but I cant seem to find the solution.
Could someone please help me?

Thanks,
Best regards

You could introduce a helper column in Sheet1 with a formula like this in F2:

=IF(A2="","",A2&"_"&COUNTIF(A$2:A2,A2))

When you copy this down you will have a unique reference for each record, and then you can extract those on Sheet2 using an INDEX/MATCH combination. Attach a sample workbook so I can give you the formulae in that, so you don't have to translate them into Spanish.

Hope this helps.

Pete

Originally Posted by TheCman81

How many columns of data do you have? Is it just the 5 in your image?

Hi, Thanks for your reply,

I have just 5 columns, but I have 76571 rows that have to become 14966

Originally Posted by pachorradas

(note: the data may not always coincide with the "header" row (row 1) on the "formated" tab. This is ok, I will deal with that later, the big issue for me is getting the data in one row)

Do you really want to have all the MOD's together in adjacent columns, then all the UNAV's, then the SES's etc. In the sample file you have up to 12 records for each ID, so a lot of the MOD columns will be empty for those ID's which have fewer. It would be better (easier) to have a block of MOD, UNAV, SES and FADE in adjacent columns, and then repeat this going across.

Hope this helps.

Pete

Originally Posted by Pete_UK

Do you really want to have all the MOD's together in adjacent columns, then all the UNAV's, then the SES's etc. In the sample file you have up to 12 records for each ID, so a lot of the MOD columns will be empty for those ID's which have fewer. It would be better (easier) to have a block of MOD, UNAV, SES and FADE in adjacent columns, and then repeat this going across.

Hope this helps.

Pete

Hi,

You are rite, there are cases where there are only 6 MOD's (or less) and other cases where there are 12. Unfortunately I haven't been able to format it all yet so that all of the ID's will have 12 MOD's (even though some of the SES, unavailability or fade_margin fields will be empty, this is important). I´m still working on "standardizing" the format and adding all 12 MOD's to all the ID's, but unfortunately its going to take me a while because now I have a total of almost 180000 rows I have to classify. Tomorrow I will upload another small sample so you can see what I mean. The format will be a bit different from the screenshot I originally uploaded but it will be more organized and clearer to understand.
Hopefully once the "raw data" is organized this wont be too complicated.

Thank you very much!

This is what I have so far. I put the formula that I gave you earlier in F2 of the original sheet and copied it down (coloured blue). Then I re-arranged your formatted sheet to show blocks of MOD, UNAV, SES and FADE in adjacent columns, extended out to 13 (column BA) and then used this formula in B2:

=IFERROR(INDEX(INDEX(original!$A:$E,,MOD(COLUMNS($B:B)-1,4)+2),MATCH($A2&"_"&TRIM(RIGHT(B$1,2)),original!$F:$F,0)),"")

This can be copied across and then down to complete your table.

If you really want it the other way, then I shall work on a version of that.

Hope this helps.

Pete

It wasn't so different doing it the other way. In the attached file I've added another sheet, formatted_2, with all the MOD's together (out to 13), then the UNAV's, and so on, and then put this formula in B2:

=IFERROR(INDEX(INDEX(original!$B:$E,,INT((COLUMNS($B:B)-1)/13)+1),MATCH($A2&"_"&TRIM(RIGHT(B$1,2)),original!$F:$F,0)),"")

The red parts show the bits that have changed. Then this can be copied across and down, as before.

Hope this helps.

Pete

Originally Posted by Pete_UK

It wasn't so different doing it the other way. In the attached file I've added another sheet, formatted_2, with all the MOD's together (out to 13), then the UNAV's, and so on, and then put this formula in B2:

=IFERROR(INDEX(INDEX(original!$B:$E,,INT((COLUMNS($B:B)-1)/13)+1),MATCH($A2&"_"&TRIM(RIGHT(B$1,2)),original!$F:$F,0)),"")

The red parts show the bits that have changed. Then this can be copied across and down, as before.

Hope this helps.

Pete

This second one is more like what I was looking for. I still have to finish the final format (I wont be able to work on it until tomorrow) But I think I will be able to use your formula to adapt it to the final format I am going to use (it is very similar in structure except for the MOD's, I won't be including them in the matrix, only as a header (row 1) (ie: SES_QPSK, SES_16QAM, FADE_64QAM, etc...) as it will be replacing the current " MOD_1, MOD_2, SES_1, SES_2 etc " header. Other than that it should be the same... I think).

If for some reason I cant get it to work with the new format I will post here again.
Also if it works I´ll let you know that the issue was solved.

Thank you very much again for your help!!. :-)
Best regards.
John

Originally Posted by Pete_UK

It wasn't so different doing it the other way. In the attached file I've added another sheet, formatted_2, with all the MOD's together (out to 13), then the UNAV's, and so on, and then put this formula in B2:

=IFERROR(INDEX(INDEX(original!$B:$E,,INT((COLUMNS($B:B)-1)/13)+1),MATCH($A2&"_"&TRIM(RIGHT(B$1,2)),original!$F:$F,0)),"")

The red parts show the bits that have changed. Then this can be copied across and down, as before.

Hope this helps.

Pete

Hi,

I managed to get a copy of the excel and work on it from home. I finished the formatting and tried to modify your formula to fit the new format, but I´m afraid I´m doing something wrong, the field is just white, no error or anything, I´ll attach a sample. the formula I modified is in B2 of the "Formatted" tab. I cant figure out whats wrong with it... then again, I don't fully understand the formula either so I probably messed something up.

Thanks

You've changed things round quite considerably (we call it moving the goalposts!). However, it only requires a small change to the formula you have in B2 of the Formatted sheet:

=IFERROR(INDEX(INDEX(Original!$C:$E,,INT((COLUMNS($B:B)-1)/12)+1),MATCH($A2&MID(B$1,FIND("_",B$1),10),Original!$B:$B,0)),"")

Changes shown in red. Copy this across and down as required.

Hope this helps.

Pete

Originally Posted by Pete_UK

You've changed things round quite considerably (we call it moving the goalposts!). However, it only requires a small change to the formula you have in B2 of the Formatted sheet:

=IFERROR(INDEX(INDEX(Original!$C:$E,,INT((COLUMNS($B:B)-1)/12)+1),MATCH($A2&MID(B$1,FIND("_",B$1),10),Original!$B:$B,0)),"")

Changes shown in red. Copy this across and down as required.

Hope this helps.

Pete

THANK YOU SO VERY MUCH!!!

the formula you gave me : =IFERROR(INDEX(INDEX(Original!$C:$E;;INT((COLUMNS($B:B)-1)/12)+1);MATCH($A2&MID(B$1;FIND("_";B$1);10);Original!$B:$B;0));"")
Worked perfectly!! It´s exactly what I needed.

Thanks!
Best regards!
John

Well, I'm glad we got there eventually.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete