How To Match Data In Two Columns And Also Move Data To Appear On Same Row
Sep 25, 2013
I have names is Column A (about 200) and names in Column B (about 800) along with additional info in other columns. Column A was just added to an existing spreadsheet and will be deleted once I accomplish my task. The goal is to come up with the 200 names in column A along with the additional info in the other columns (address, phone) and to delete all of remaining names in B (about 600, 800-200) I'm trying to figure out the most efficient way to do this. I'm not sure if there is a function to match the text in Column A to Column B and at the same time move the row in Column A to match the row the name is located in Column B.
For example John Smith is currently in A1 and John Smith is in B7 and then John's phone number is in C7. I want the John Smith in A1 to move to A7. If I can make that happen for all 200 names I will then delete all rows with no entry in column A.
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
Date Product Pass Fail Repair Group Product 1 P F R Group Product 2 P F R Group Product 3
[code]....
I currently have raw data in Sheet 2
On sheet 1 would like to get the summary of the data as posted in the picture... unfortunately there are "groups" in the raw data and I need them separated and conformed to get an accurate picture.
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
I have a big table with a lot of data in it. I basically want to get rid of all of the empty cells, so that all the data that is there for the one person is all next to each other.
All the people's names are in a row for themselves so the empty cells would have to move to the left.
So, I have a column with data on rows as follows below. I need to arrange the data below in such a way that I have on first column the company name, second column the contact person and so on depending on what data is found (tel, e-mail, website).
The data is on rows and I have separated each company/group of data with a row between them. There are in total aprox 200 companies/groups of data that I need to arrange as explained above.
I have tried with transpose but I have to manually do it 200 times. I have tried with an indirect formula but the companies/groups of data do no have same amount of info/rows e.g. some lack the phone number or other data.
if there is a method to save the time and not arrange them manually.
Actinote Contact: Toine Kets Managing Director Str. Tache Ionescu 3, Et. 5, Apt. 10
If the row contains a text like ABC in multiple columns in a sheet it has to copy the whole column from that sheet and paste special into another sheet.
I am trying to move information that is pulled from a database to look cleaner and easier to read. All of the information exports out into column A only. The attachment is a brief example of what it looks like, and what I’m trying to get it to look like.
I want to move the data in col A to 5 columns C through G in groups of 16 rows. Once the last column is filled with a group of 16, it needs to fill below the first group of 16 in column B onward to column F. It should repeat this process until there is no more data in A. The attachment shows it much better than i can explain it.
I have some basic experience with formulas and macros, but I haven’t had any luck trying to manipulate any of them to give me the information the way I have shown. I would prefer to not have to manually type or cut and past the information in if possible.
I have a table with one column of data. The data in this column repeats with 4 relevant pieces of information that I want to put in 4 different columns (fields) in a different spread sheet (or the same would work better and I would just delete the first column when done) keeping the same order the data is now in. The data currently repeats in a regular pattern (i.e. 123412341234 with no other data in between). I would like to do this with a macro. Could someone help write a macro that will do this
I saw this thread from June Cut & Paste Macro: Move Address From Column To Row and I think this what I would like to have my macro do. I have one column which is copied and pasted as text in excel and there is several blank rows.
I attached the spreadsheet example - it has 40 lenders, with the top row being the lender name (A1), followed by address(A2), city-state (A3), two blank rows(A4-A5), lender type (A6), approved date (A7), one blank row (A8), lender specialty (A9), telephone (A10) , e-mail address(A11) and two blanks rows (A12-A13) and A14 starts over again. For this example there should be 8 columns and 40 rows (lender name, address, etc...). As I mentioned I think the previous thread's macro would work with some minor tweaks. I couldn't figure out the best way to handle the blank rows and or remove the blanks and what to add or take out of the macro code.
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA AAA - description BBB BBB - description CCC CCC - description and so on..
I would like to move the descriptions from column A to column B: AAA AAA - description BBB BBB - description CCC CCC - description and so on...
I have a sheet which I need to arrange and it looks like -
Column A Column B Column C Column D
[Code]...
the values in Column B, C , D , E & F need to be in new columns based on what we have in Column A associated with "1"
Output should be - Column A Column B Column C
[Code]....
so whatever is in Column A associated with "0" should remain in place whereas whatever is in Colimn A associated with "1" should get added to the new columns. 0 and 1 resembles the 1 Records which are used as a split to normalize the data.
I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.
I have a worksheet that has four columns. FName LName FName1 LNanme1. The data doe snot match between the columns... but I need to know which are in both columns.
I need to write a formula that will allow me to compare the LName with LName1 and give me some type of indication there is a match (1, yes, etc.). Ideally, after confirming there is a match, It would create another verification that FName and FName1 also match, but that is not necessary.
I basically have a list of 4000 names in one column (A) and an extract from a report containg 26000 names, again in one column (B). I need to see which of the 4,000 names in (A) appear in the column (B) from the report.
How can I do something to either line up the names, or make them change colour or something so I can see the matches?
I am trying to match up data from 2 columns and then take data from an adjacent column. I.e. take info in cell 2B, search column D for the same data and grab the data (qty) from the adjecent cell (E).
Column A has donor name, column B has corresponding donation data for 2011. Column C has donor name, and column D has donation data for 2012. Column E has donor name, and column F has corresponding donation data for 2013.
Although Columns A and C and E all have donor names, there are many changes from year to year with the addition and deletion of certain donors so the rows do not match up exactly. Is there a quick way to have the rows match up according to donor names in column each of the three columns? Secondarily, I need to sort by donation amount from highest to lowest (it can either be the total over the three years or based on any single year.
Currently my list looks like this: 2011 2012 2013 A A A B C B D D C F E D H F I L G K M H M
I would like it to look like this: 2011 2012 2013 A A A B - B C C - - D- - E- F F F G G G H - - L - L - - M
Basically, I want to see the year over year giving for each donor. I've attached a sample for refrence. (I can't post the original due to confidentiality requirements.)
1. i am comparing text 2. i have >100 countries, and with vlookup i have to type every single one, and this is what i want to escape (mb i need to refer to an array, or string, or no idea) 3. mb a solution could be sth with index, or match, i just tried it, copying some similar formula from www, but it didn't match the countries, and return the value respective for that country.
E.g. I have China in col1 (1st file), #employees 10 in col2 (1st file), and China in Col 1 in 2nd file. So, a formula should match China & China from both files, and return 10 as number of employees.
I have a volumes sheet and based on two criteria (in columns) am trying to pull the corresponding value from the April Results Table. I have product type in C2 (this varies down the column) and depot numbers in D2. The table i am trying to match has depot numbers in column A2:A93, and Product type in Row 2 (B2:P2), where the two criteria are matched, i need it to return the corresponding figure. I have tried various combinations of Index and Vlookups but i cant get it to work!
I have 3 columns of data and 30,000+ rows. Column A & B are related and column C contains some of the same values as column B but not an exact match. I'm looking for a way to sort columns A & B so that columns B & C match up and the data from column A is moved as well. For any data that has no match, a blank space should be input. Please see below.
A B C
red dog cat blue cat dog green mouse pig yellow horse kitten
A B C
blue cat cat red dog dog green mouse pig yellow horse kitten
I wish to add more criteria to the "Find Feature to Find 3 Matching Criteria in 3 Columns in Excel" (http://www.ozgrid.com/VBA/advanced-find.htm) up to 7 criteria if possible. How can I do this? I wish to display the results in a dialog box or in a different sheet. I also want to run the find feature from a different worksheet, perhaps using a button instead of having to make a selection in the table.
I am using Excel 2007 and I need to search for data patterns that are spread across many columns. I am not sure how to search so that criteria is met across all the columns concurrently.
For example
I am interested in data that matches the following criteria ;
Column B has the value 55 Column C has the value 70 Column D has the value 80
Rows of interest are when all the criteria in columns B,C,D are met at the same time (e.g 55 in B, 70 in C and 80 in D).
In an Excel sheet; I am trying to match and align data in column C to data in column A, but it is essential that the data contained in the entire row (coln B thru to coln W) moves when Column C is matched and aligned with column A.
Any formula that can look at the data in multiple columns and check for a match - returning a value such as YES or NO. Below is an example of my sheet.
A B C D E F G
1 Name Number Name Number Name Number Match?
[Code] ..........
Basically the formula would be in Column G and would look at B, D, & F to see if they match or not. In the example above - row two - they all match. So a True, 1, or Text option such as YES would be great. Same as row 3 only you can see they are different.
Please see the attached example. I'm trying to highlight cells only if 2 values in the row match 2 values in another row. If a book title/author combination matches that of another title/author combination in a different row, it would highlight. Some alternate rows will contain different titles by the same author, and some rows will have like titles by different authors...but they should not highlight.
The only situation where highlighting would occur is if the title/author pairing appears in another row.
I'm trying to decide if this would be a conditional formatting/highlighting rule, or if it would be a macro/vbs?
