Previous Topic Next Topic

Getting atributes of one coloumn in another..

Closed
Robert - Dec 4, 2009 at 05:50 PM
 Robert - Dec 7, 2009 at 04:09 AM
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 responses

Answer 1 / 6
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 4, 2009 at 09:20 PM
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
0
Answer 2 / 6
Robert
Dec 5, 2009 at 12:20 AM
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
0
Answer 3 / 6
Robert
Dec 5, 2009 at 12:59 AM
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
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 5, 2009 at 11:33 PM
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.
0
Answer 4 / 6
Robert
Dec 6, 2009 at 02:37 AM
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.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
Robert
Dec 6, 2009 at 02:46 AM
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.
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 6, 2009 at 08:46 PM
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
0
Answer 6 / 6
Robert
Dec 7, 2009 at 04:09 AM
It works ..thanks a lot venkat
0