Getting atributes of one coloumn in another..

Closed
Report
-
 Robert -
Hello,
This is getting on my nerves..i can't seem to know how to do this.
So basically i have a table like this
Id Artist Coloumn C
278 009 Sound System 009 Sound System
279 2Pac and Talent 2Pac and Talent
280 3 Doors Down 3 Doors Down
281 30 Seconds to Mars 3 Doors Down
151 3Oh!3 3 Doors Down
137 50cent 30 Seconds to Mars
283 6TreG 30 Seconds to Mars
284 A Brides Guide To Wedding Music 3OH!3
285 Aaron Copland and London Symp 3OH!3
286 Aaron Neville 3OH!3
287 Academy Of Choir Art Of Russia 50 Cent
289 Academy of St Martin in the Fields 50 Cent
288 Academy of St Martin in the Fields and Sir 50 Cent
290 Adam Lambert 6TreG

and it continues with 1000 more rows. Column B has unique data and column C doesn't. What i would like to do is get each that is the corespondent for the artist and place it in column D next to it's corespondent.
It should look like this:

Id Artist Coloumn C ID
278 009 Sound System 009 Sound System 278
279 2Pac and Talent 2Pac and Talent 279
280 3 Doors Down 3 Doors Down 280
281 30 Seconds to Mars 3 Doors Down 280
151 3Oh!3 3 Doors Down 280
137 50cent 30 Seconds to Mars 281
283 6TreG 30 Seconds to Mars 281
284 A Brides Guide To Wedding Music 3OH!3 151
285 Aaron Copland and London Symp 3OH!3 151
286 Aaron Neville 3OH!3 151
287 Academy Of Choir Art Of Russia 50 Cent 137
289 Academy of St Martin in the Fields 50 Cent 137
288 Academy of St Martin in the Fields and Sir 50 Cent 137
290 Adam Lambert 6TreG 283

Do you have any idea how i can accomplish this?...thansk a lot for help. This is killing me.
Rob

6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
It is not clear what is artist and what is colum c

put up slash(/)between the column values and post back
e.g.(if this is correct) in the first list
similarly in the second list.

278 /009 Sound System/ 009 Sound System
I'm sorry, i'll try to explain again.
The 1st two columns are related to each other(For each cat id there is only one artist). The 3rd one is not. What i want to do is verify if every element from column B is in column C, and if it is it should put in column D the corespondent element to column B from Column A.Let me show you an example:
Id                  Artist                          Coloumn C
278     009 Sound System          009 Sound System
279      2Pac and Talent               2Pac and Talent
280     3 Doors Down                   3 Doors Down
281     30 Seconds to Mars           3 Doors Down 

For each artists in column B(ex: 2Pac and Talent) i need it to compare it to each entry in column C, and if the two data match(all 4 entries in the example match) than, in column D, it should return the Id from column A corespondent Artist(For 3 Doors Down Artist the id is 280 and in column D it should put 280 every time in column C appears 3 Doors Down)
The table should look like this:
Id                  Artist                          Coloumn C       Id
278     009 Sound System          009 Sound System            278
279      2Pac and Talent               2Pac and Talent             279
280     3 Doors Down                   3 Doors Down                280
281     30 Seconds to Mars           3 Doors Down                280

And it continues with over 1000 rows.
I hope i was clearer this time.I don't know why it is so hard to explain.
Thanks,
Rob
Trying to make the columns look better so you can understand what i need:
This is the table i have:

  Id            Artist                  Column C
279           2Pac and Talent          2Pac and Talent
280           3 Doors Down             3 Doors Down
281           30 Seconds to Mars       3 Doors Down
151           3Oh!3                    3 Doors Down
137           50cent                   30 Seconds to Mars
283           6TreG                    30 Seconds to Mars
284           A Brides Guide           3OH!3
285           Aaron Copland            3OH!3
286           Aaron Neville            3OH!3
287           Academy                  50 Cent
289           Academy                  50 Cent
288           Academy                  50 Cent
290           Adam Lambert             6TreG


And here is the table i need with column D grabbing id's from column A in regards to what artist it corresponds to(but now the id should correspond to column C).


  Id            Artist                  Column C         ID's Column D
279           2Pac and Talent          2Pac and Talent        279
280           3 Doors Down             3 Doors Down           280
281           30 Seconds to Mars       3 Doors Down           280
151           3Oh!3                    3 Doors Down           280
137           50cent                   30 Seconds to Mars     281
283           6TreG                    30 Seconds to Mars     281
284           A Brides Guide           3OH!3                  151
285           Aaron Copland            3OH!3                  151
286           Aaron Neville            3OH!3                  151
287           Academy                  50 Cent                137
289           Academy                  50 Cent                137
288           Academy                  50 Cent                137
290           Adam Lambert             6TreG                  283

Of course, column C is longer because it has the duplicates as well, so the column D length should be exactly like column's C's length.
Thanks,
Rob
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
Is this what you want

the comma is delimiter .

Id, Artist, Column C, col D
279, 2Pac and Talent, 2Pac and Talent, 279
280, 3 Doors Down, 3 Doors Down, 280
281, 30 Seconds to Mars, 3 Doors Down,
151, 3Oh!3, 3 Doors Down,
137, 50cent, 30 Seconds to Mars,
283, 6TreG, 30 Seconds to Mars,
284, A Brides Guide, 3OH!3,
285, Aaron Copland, 3OH!3,
286, Aaron Neville, 3OH!3,
287, Academy, 50 Cent,


if so the formula in D2 is
=IF(B2=C2,A2,"")
copy D2 down.
Hello,
Thanks venkat for your input but that is not what i need.
I tried editing your forumula into:

=IF(B2=$C$2:$C$1260,A2,"!!!!!!")

Because i need B2,B3,B4,B5 to be checked with the hole C column but the formula i tried just seems to not work. All it returns is the false value even tho there are a lot of columns that match.

I think i have to use some other function.
Ok ..i tried a little more formulas and it doesnt seem to work. I tried this one:

=IF(C2=$B$2:$B$726,A2,"!!!!!!")
but it doesnt work. I need something like this:

=IF(C2=$B$2:$B$726,get the id from column A corespondent with the cell in column B where it found it,"!!!!")

The formula i tried only works when the data is on the same row.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
IN D2 copy this formula

=INDEX($A$1:$A$100,MATCH(VLOOKUP(C2,$B$1:$B$100,1,FALSE),$B$1:$B$100,0),1)=INDEX($A$1:$A$11,MATCH(VLOOKUP(C2,$B$1:$B$11,1,FALSE),$B$1:$B$11,0),1)

copy D2 down till the data is there
I assume there are not more than 100 rows.
the result will be as follows (if this is what you want post confirmation)

Id Artist Column C
279 2Pac and Talent 2Pac and Talent 279
280 3 Doors Down 3 Doors Down 280
281 30 Seconds to Mars 3 Doors Down 280
151 3Oh!3 3 Doors Down 280
137 50cent 30 Seconds to Mars 281
283 6TreG 30 Seconds to Mars 281
284 A Brides Guide 3OH!3 151
285 Aaron Copland 3OH!3 151
286 Aaron Neville 3OH!3 151
287 Academy 50 Cent #N/A
It works ..thanks a lot venkat