E X C E L Experts Come Here

[aytida]

Need help baa.

I have 2 excel matrices with data.
pinky.xslx and ponky.xslx has 2 vertical columns of data in columns A and B rows.
Some of which matches column A to A and B to B rows respectively.

1. How can I verify if data in A matches data in A and weed out the duplicates
2 Is there a way that when data in A matches A, can I replace B to B one excel to another


Example: Matrix pinky.xlsx
A B
1 Apple Good
2 Boy Bad
3 Cat Ugly
4 Dog I
5 Egg We
6 fan Me

Matrix ponky.xslx

A B
1 Andhra Good
2 Buffalo Bad
3 Apple Ugly
4 Dog I
5 Fan We
6 Egg Me

Above, if how can we match A1=A3? similary all matches of pinky A to ponky A
Also, if A1=A3, then How can I replace B1 on pinky to B3 of ponky



All help is greatly appreciated..
Thank you guys &>:! &>:! &>:!

[charygaru]

pivot tables vaadachhu ani telsu ee situation ki kaani elta vaadaali anedi telvadu mama youtube video lu vethiki cheptha

[@Shadow@]

pinky column-A and ponky column-A sort them first in ascending order
go to ponky column C and try VLOOKUP with column Bof pinky............ providing pinky as a parent (and doing this in ponky makes it as child)
now you will get all pinky's B column values into C column of ponky (these values are only matched values of column A)

[@Shadow@]

then replace only those values in columns C (existing values not null)with column B of ponky

[aytida]

[quote name='@Shadow@' timestamp='1351816563' post='1302728065']
pinky column-A and ponky column-A sort them first in ascending order
go to ponky column C and try VLOOKUP with column Bof pinky............ providing pinky as a parent (and doing this in ponky makes it as child)
now you will get all pinky's B column values into C column of ponky (these values are only matched values of column A)
[/quote]


Formulas rakane baa, post vesanu.

Unte cheppa radhu, kottukuntunnanu ikkada

[king420]

nee post lo maaku konni abhyantara kara sannivesalu unnai

[_poori]

#1. Copy data from Ponky.xlsx to the same sheet in Pinky.xlsx into, say, Columns E and F. Select all the values in Column E (aka Column A in Ponky.xlsx) and give the range a name in the upper left-hand corner (PonkyRangeA).

now in Column C, put the formula "[b]=VLOOKUP(A1, PonkyRangeA, 1, FALSE)[/b]"

This will put the same cell value in Column C for all the values in A which have a match in Column E (data from Ponky.xlsx).. appudu you know which ones are common in both..

[@Shadow@]

[quote name='aytida' timestamp='1351816737' post='1302728076']


Formulas rakane baa, post vesanu.

Unte cheppa radhu, kottukuntunnanu ikkada
[/quote]

no formula needed, just try to get some idea on how VLOOKUP works
anthey, you problem resolved

[ChampakDas]

lessa palikithivi mitrama...

edhi programming lo aithe easy..if you know programming else ignore..
1.just load that columns into dictionary<string,string> pair petuko. Now you have got two dictionaries.
2. Foreach petu for any of the dictionary...for each string check if ColumnA value exists in dictionary2 ColumnA..ela anukuntunnav .Contains method tho
3. okavela match data dhorikithey from point 2...since you know the index..you can do what ever you want..
[quote name='@Shadow@' timestamp='1351816563' post='1302728065']
pinky column-A and ponky column-A sort them first in ascending order
go to ponky column C and try VLOOKUP with column Bof pinky............ providing pinky as a parent (and doing this in ponky makes it as child)
now you will get all pinky's B column values into C column of ponky (these values are only matched values of column A)
[/quote]

[king420]

[quote name='ChampakDas' timestamp='1351817002' post='1302728098']
lessa palikithivi mitrama...

edhi programming lo aithe easy..if you know programming else ignore..
1.just load that columns into dictionary<string,string> pair petuko. Now you have got two dictionaries.
2. Foreach petu for any of the dictionary...for each string check if ColumnA value exists in dictionary2 ColumnA..ela anukuntunnav .Contains method tho
3. okavela match data dhorikithey from point 2...since you know the index..you can do what ever you want..
[/quote]
Brahmi kind padi kottukune gif marchipoyav [img]http://www.gifsoup.com/view1/1351852/nethonakentibe-o.gif[/img]

[_poori]

#2. Column E and F (data from Ponky.xlsx) ni motham okka range name ivvu (RangeEF).. and now in Column D you can use the formula

[color=#282828][font=helvetica, arial, sans-serif]"[b]=VLOOKUP(A1, RangeEF, 2, FALSE)[/b]"[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]This will put the value from Column F (aka Column B from Ponky.xlsx) for all the values in Column A which match Column E[/font][/color]

[ChampakDas]

[img]http://www.gifsoup.com/view1/1351852/nethonakentibe-o.gif[/img][img]http://i1354.photobucket.com/albums/q681/mutamesthri/temp-300-81688885_zpse8930264.gif[/img]
[quote name='HAPPYNESS' timestamp='1351817113' post='1302728108']
Brahmi kind padi kottukune gif marchipoyav [img]http://www.gifsoup.com/view1/1351852/nethonakentibe-o.gif[/img]
[/quote]

[ChampakDas]

well done pardhu [img]http://www.gifsoup.com/view1/1351852/nethonakentibe-o.gif[/img]
[quote name='_poori' timestamp='1351817204' post='1302728114']
#2. Column E and F (data from Ponky.xlsx) ni motham okka range name ivvu (RangeEF).. and now in Column D you can use the formula

[color=#282828][font=helvetica, arial, sans-serif]"=VLOOKUP(A1, RangeEF, 2, FALSE)"[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]This will put the value from Column F (aka Column B from Ponky.xlsx) for all the values in Column A which match Column E[/font][/color]
[/quote]

[king420]

[quote name='_poori' timestamp='1351817204' post='1302728114']
#2. Column E and F (data from Ponky.xlsx) ni motham okka range name ivvu (RangeEF).. and now in Column D you can use the formula

[color=#282828][font=helvetica, arial, sans-serif]"=VLOOKUP(A1, RangeEF, 2, FALSE)"[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]This will put the value from Column F (aka Column B from Ponky.xlsx) for all the values in Column A which match Column E[/font][/color]
[/quote]
inthamandi salahalu iste chivarakiii em cheyyalo teleyakaa thread owner... [img]http://www.gifsoup.com/view1/1351852/nethonakentibe-o.gif[/img]

[@Shadow@]

[quote name='ChampakDas' timestamp='1351817002' post='1302728098']
lessa palikithivi mitrama...

[/quote]


[quote name='HAPPYNESS' timestamp='1351817113' post='1302728108']
Brahmi kind padi kottukune gif marchipoyav [img]http://www.gifsoup.com/view1/1351852/nethonakentibe-o.gif[/img]
[/quote]