Name Identification and Format from different reports

lyonstl03

New Member
Joined
Jun 12, 2020
Messages
13
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
The work book has three work sheets.
I have two separate worksheets (1 and 2) that are reports that provide employee names two different ways. Last, First and First Last. My issue comes when I have to compile numbers per employee into one worksheet (3) based on different metrics. Is there a way for worksheet 3 to see the data by name no matter the name format.

Ex - This is Sheet 3 which is what I'm trying to do to compile information from both reports to get percentages, errors, etc. It's just the name thing is messing with me.
Smith, John (C2)Apr-19 (D2)77 (Sheet1 COUNTIFS here)
0 (Sheet2 COUNTIFS here)​
0​
N/A
0​
0​
N/A


=COUNTIFS('Sheet1'!A:A,$D2,'Sheet1'!$B:$B,$C2)

=COUNTIFS('Sheet2'!A:A,$D2,'Sheet2'!$E:$E,$C2,'Sheet2'!B:B, "Reg Approved")

Sheet1 Report
4/1/2019​
Smith, John $BApproved02/06/201307/02/201803/03/2013NoNo1234No12345671234567Open0Program

Sheet2 Report
4/1/2019​
Reg ApprovedDataDataJohn Smith $ECustomer Name123456784/17/20204/21/2020NO4/22/20205NData



So, the names in Sheet1 are in column B and are formatted (Last, First) and the names in sheet two are in column E and formatted (First Last).

Is there a solution without changing the names on these reports?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

lyonstl03

New Member
Joined
Jun 12, 2020
Messages
13
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I tried using the following :

=MID(B2&" "&B2,FIND(", ",B2)+1,LEN(B2))


I put that formula in column P of one of the reports which automatically switches all the names to First Last, but when I change my Sheet1 formula to grab the name from P then it isn't recognizing the name in the report.
 

lyonstl03

New Member
Joined
Jun 12, 2020
Messages
13
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I've tried finding some more information about how to recognize the value or text of a cell instead of the formula itself. I'm still stumped.
 

lyonstl03

New Member
Joined
Jun 12, 2020
Messages
13
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
I believe I've solved this issue using TRIM.

=TRIM(MID(B2&" "&B2,FIND(", ",B2)+1,LEN(B2)))

That now picks up the actual data in the COUNTIFS formula instead of the formula that was used to switch the names.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,747
Messages
5,626,628
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top