Cambiar texto en columnas por número asignado en una columna específica

Tengo un archivo de Excel semanal que recibo, el cuál trae en una celda de cada fila con varios nombres de personas (solo el primer nombre) y en una celda de cada fila un valor numérico que puede variar cada semana, cada nombre debe llevar un contador en otra hoja, ejemplo en A1 Pedro, Juan, Carlos, en D1, viene el valor 5, la siguiente hoja viene en A1 Pedro, A2 Juan, A3 Carlos y en la siguiente columna, la B1 al B3 debe llevar el valor sumado más el que ya tiene acumulado, lo que hago actualmente es usar la opción "texto en columnas" para dividir los nombres y luego voy uno a uno sumando valores en la siguiente hoja, lo que quisiera saber es si hay forma de agilizar esto con alguna fórmula anidada o un macro