display matching data in new column

[sarat47]

Hi,

In the attached sheet, I have two sub sheets "Inform" and "Company".

My requirement is to develop one macro to insert two new columns (Teams, Statuscheck) in sheet "Inform" after first column i.e. "Name".

For those inserted new columns, I want to populate the data from sheet "Company" if user names are matching between two sheets ( Inform.Name=Company.C_Name)

Thanks in advance for your help

[walruseggman]

Please Login or Register  to view this content.

[joe31623]

Walrus,

You came up with a solution much quicker than I could! I'm still learning...

On that note, does this line: For Each c In Range("A2:A" & ws1.Cells(Rows.Count, 1).End(3).Row) loop over ALL rows in the entire worksheet, regardless of whether or not there is content in the cells or is excel smart enough to only loop over the current region? I thought Rows.Count with no reference to a range will return all rows in the worksheet but I could be wrong.

Thanks in advance if you have time to comment on your solution.

[walruseggman]

Please Login or Register  to view this content.

Will return the row number of the last row in column 1 with a value in it. So, in theory, if there were a couple blank cells in column 1, and then a continuation of cells with values afterwards, the code would still run on those blank cells.

Not usually a big deal, especially if the data set is small, or as in this case, the OP provides a sample that shows no blank cells.

However, if time is a crucial factor, or there are a ton of blank cells in your data set that you don't want the code to waste it's time on, one very easy solution is to use SpecialCells. For example, this would run on only the cells in column 1 (minus cell A1) that have text values in them, and would ignore any blank cells, cells with formulas, cells with numbers in them, etc.:

Please Login or Register  to view this content.

Hope that helps.

[sarat47]

Thank you very much for your great support.

The user contain some extra space for which the column is not populated (e.g. for user 'Rakesh mehra' in row number 4, the team column is blank where as the same populated in rownum 2).
Could you guide where the trim function should be used.

[joe31623]

Sara,

The reason "Rakesh mehra" was not found on the "Inform" worksheet was probably because the search method used distinguished between "Rakesh mehra" and "Rakesh Mehra". I noticed there were some trailing and leading spaces that can also cause some issues, depending on how the solution is coded. The following code is robust enough to handle trailing and leading spaces in addition to capitalization errors. Any other mistakes such as misspellings, forgetting the ".", or two spaces between the first and last name will result in the Find method used in the code below to miss the name on the "Inform" worksheet.

If a name is not found, I displayed a Message box specifying which name was not found. You can comment that line out if you don't want the message box.

I debugged and tested the following code on your test worksheet. Let me know if there are any problems.

Please Login or Register  to view this content.

[sktneer]

You may also try something like this.....

Please Login or Register  to view this content.

In the attached, click the button on Inform Sheet to run the code and see if this is what you were trying to achieve.

[sarat47]

Thank you very much for your kind support.

[walruseggman]

To handle trailing and leading spaces, as well as capitalization:

Please Login or Register  to view this content.