Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side
I have an excel issue that if someone could help me figure out, would
save a lot of time. Here is the situation: I currently have 2 databases exported together into excel side by side. On the left side there is database 1 - a huge column of company names (column A) with their account #s in an adjacent column (B). On the right side of the worksheet there is database 2 - a huge column of the same company names (however some of these names have been named a bit differently than the first DB....yet still the same companies are all there - column C) along with other columns that deal with important information concerning each company (columns D-H). This is what I need to do....I need to go through the list of DB 2 and find their corresponding company name + account # from the two DB 1 columns (I hope that made sense). In order to do this manually...I will have to scroll down DB 1 looking for the right co name that is similar to the one I am matching in DB 2. Once I have found a match somewhere, I need to cut 2 cells (co. name and account # from DB 1) then insert them side by side the similar account name in DB 2 so it all lines up. So ultimately....I should have matching account names with their account #s and other info sitting side by side instead of all over the place. Now this is what I want Excel to be able to do: perhaps I can create a macro that will allow me to search for "part" of the co. name (unique identifier) in the DB 1 column, then it will hopefully grab the correct cell along with its adjacent account # cell...cut them both and paste them above the cell selected...so in the end I will have the correct DB1 account # and name next to its DB2 data and all I would have had to do this whole time was select the right cell to paste it into and search under a certain name criteria. I understand this wouldn't work 100% of the time (in which case I would have to take those few and manually do them), but it would save a person A LOT of time in the searching, cutting, and pasting routines. - I am not sure if this is more of an Access query problem, but it would be better if it could be solved in Excel. Please let me know if anyone can help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side
Thank you Dave,
These links which I have seen before (through my browsing of the google groups) didn't seem to do "exactly" what I needed. However, I did find a macro script that helped me. I tweaked it a bit to highlight cells in both columns that have matching text. Afterwards I created 2 new columns on both sides and put in a formula to tell me the color code of the cell background (eg. whether it is highlighted yellow or just plain white - meaning no match). Then I sorted the data by color code and I was able to segregate exact matches from the ones I would have to match up manually. Although I didn't completely solve my problem...I fixed almost half of the entries in little time which does a lot for me. If anyone else has this problem, you can email me at . On Jun 8, 11:46 am, Dave Peterson wrote: You may be able to do something with =index(match()) or =vlookup(). Debra Dalgleish has lots of notes on =vlookup() and =index(match()) hehttp://www.contextures.com/xlFunctions02.html (for =vlookup()) andhttp://www.contextures.com/xlFunctions03.html (for =index(match())) I've never seen a partial match work close enough to help. I'd do my best to match up the exact matches and then a manual effort for the mismatches. You may also want to look at some of the techniques that Chip Pearson uses:http://www.cpearson.com/excel/duplicat.htm wrote: I have anexcelissue that if someone could help me figure out, would save a lot of time. Here is the situation: I currently have 2 databases exported together intoexcelside by side. On the left side there is database 1 - a huge column of company names (column A) with their account #s in an adjacent column (B). On the right side of the worksheet there is database 2 - a huge column of the same company names (however some of these names have been named a bit differently than the first DB....yet still the same companies are all there - column C) along with other columns that deal with important information concerning each company (columns D-H). This is what I need to do....I need to go through the list of DB 2 and find their corresponding company name + account # from the two DB 1 columns (I hope that made sense). In order to do this manually...I will have to scroll down DB 1 looking for the right co name that is similar to the one I ammatchingin DB 2. Once I have found a match somewhere, I need to cut 2 cells (co. name and account # from DB 1) then insert them side by side the similar account name in DB 2 so it all lines up. So ultimately....I should havematchingaccount names with their account #s and other info sitting side by side instead of all over the place. Now this is what I wantExcelto be able to do: perhaps I can create a macro that will allow me to search for "part" of the co. name (unique identifier) in the DB 1 column, then it will hopefully grab the correct cell along with its adjacent account # cell...cut them both and paste them above the cell selected...so in the end I will have the correct DB1 account # and name next to its DB2 data and all I would have had to do this whole time was select the right cell to paste it into and search under a certain name criteria. I understand this wouldn't work 100% of the time (in which case I would have to take those few and manually do them), but it would save a person A LOT of time in the searching, cutting, and pasting routines. - I am not sure if this is more of an Access query problem, but it would be better if it could be solved inExcel. Please let me know if anyone can help -- Dave Peterson- Hide quotedtext- - Show quotedtext- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Pivot Tables-How to put two Data Varibles side by side | Excel Worksheet Functions | |||
Display columns side by Side in the Data Area of a Pivot table | Excel Discussion (Misc queries) | |||
How do I make a stacked column chart with side-by-side comparison. | Charts and Charting in Excel | |||
Data markers in a stacked chart will not sit side by side by month | Charts and Charting in Excel | |||
Data markers in a stacked chart will not sit side by side by month | Charts and Charting in Excel |