CODEWITHSUNDEEP
excel
Iain
Iain

Reputation: 9

Missing Data in Excel between 2 columns

I'm working with a fairly large data set, I have a list of names which appear more than once in column B and in column C i have the team they are part of.

In column C which displays there team name most the data is there but I do have a few 1000 where no team name is not present. Is excel smart enough to go for example Jamie@soso is part of design because it case in "B36" "C36" and then assign design to the missing field?

Here is an example of the data with the missing team names

data with missing teams

And here is a view with what im looking for end result wise.

completed data

If I need to explain this better please let me know and thank you in advance.

Upvotes: 0

Views: 453

Answers (2)

Carol
Carol

Reputation: 471

To do without VBA, add a helper column in column C and insert this array formula:

{=INDEX($B$1:$B$17,MATCH(A1 & "?*",$A$1:$A$17&$B$1:$B$17,0))}

To create an array formula, type in the formula without the curly brackets, then press CTRL+SHFT+ENTER. Excel will add the curly brackets.

When you have your column C results, you can copy and paste values to overwrite column B if you want to get rid of your helper column.

This formula looks for rows with a value in A that matches the current row (i.e. same name), and any value in B that isn't blank; "?*" ensures at least one character. The match look up value is therefore A & non-blank B, and the array it's looking in is both A column range and B column range. The INDEX part provides the value from column B if it exists.

Names with no matching column B already completed will give you #N/A so you can manually enter those.

Upvotes: 0

teylyn
teylyn

Reputation: 35915

On the assumption that there are no ambiguous entries, you could do the following:

  • if the order of the data is important, add an index column and insert ascending numbers for all rows of data (hint: enter the first two values manually, then select the first two cells and double click the fill handle to fill all the way down to the end of the data)
  • Sort the data by name and then by team as secondary sort

enter image description here

  • select column B
  • hit F5 to open the Go To dialog
  • click Special button
  • tick the option Blanks and click OK
  • now all blank cells in column B are selected
  • without changing the selection start typing a = sign, then hit the up arrow on your keyboard.
  • hold down the Ctrl key on your keyboard and hit Enter
  • now each previously blank cell in column B will have a formula that references the cell above.
  • copy column B
  • paste special on column B and paste values to get rid of the formulas
  • use the index column created in the first step to return to the original sort order

If you want this exercise to be repeatable, you could also use Power Query. Load the data into the Power Query editor, sort by name (ascending) and team (descending), select the Team column and click Transform > Fill > Fill down. The screenshot shows the result

enter image description here

Upvotes: 2

Related Questions