Tenho dados fictícios relacionados ao código do produto dos livros:
Xxx | AAA | BB | Código do documento | Tipo de teste |
---|---|---|---|---|
P01 | VAIA | 01 | BK-001-CR | 1. Teste de qualidade de conteúdo - Revisão - Verificação de fatos - Edição final 2. Teste de design e layout |
P01 | VAIA | 02 | BK-002-CR | 1. Teste de produção física 2. Teste de distribuição |
P02 | OK | 01 | BK-001-CR | 1. Teste de qualidade de conteúdo - Revisão - Verificação de fatos - Edição final 2. Teste de design e layout |
P03 | COCO | 01 | BK-001-RT | 1. Teste de qualidade de conteúdo - Revisão - Verificação de fatos - Edição final 2. Teste de design e layout |
conforme listado neste link .
Quero limpá-lo e transformá-lo em uma saída esperada da seguinte forma:
Código do produto | Código do documento | Tipo de teste |
---|---|---|
P01.BOO.01 P02.OOK.01 |
BK-001-CR | Teste de qualidade de conteúdo - Revisão - Verificação de fatos - Edição final |
P01.BOO.01 P02.OOK.01 |
BK-001-CR | Teste de design e layout |
P01.BOO.02 | BK-002-CR | Teste de produção física |
P01.BOO.02 | BK-002-CR | Teste de Distribuição |
P03.KOO.01 | BK-001-RT | Teste de qualidade de conteúdo - Revisão - Verificação de fatos - Edição final |
P03.KOO.01 | BK-001-RT | Teste de design e layout |
conforme listado neste link .
Para a saída, tentei com a fórmula na coluna Código do Produto:
=ARRAYFORMULA(
TEXTJOIN(" "; TRUE;
FILTER(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!B2:B5") & "." &
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!C2:C5") & "." &
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!D2:D5");
(REGEXMATCH(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!F2:F5"); I2) +
REGEXMATCH(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!B2:B5"); H2)) > 0
)
)
)
Na coluna Código do Documento:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!E2:F5"); "SELECT Col1 WHERE Col2 CONTAINS '" & I2 & "'")
Na coluna Tipo de teste:
=ARRAYFORMULA(
UNIQUE(
FILTER(
TRIM(
FLATTEN(
SPLIT(
REGEXREPLACE(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!F2:F5");
"\d+\.";
"~"
);
"~"
)
)
);
(TRIM(FLATTEN(SPLIT(REGEXREPLACE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dIVsBszYwQnELHwu8C7BaamZnQTz2zogzKlrkq72IZQ/edit?gid=0#gid=0"; "Test!F2:F5"); "\d+\."; "~"); "~"))) <> "")
)
)
)
Entretanto, ainda há um erro e ele ainda não corresponde à saída esperada.
Existe alguma outra fórmula?
Aqui está uma abordagem generalizada que você pode adaptar conforme necessário:
Você também pode tentar esta fórmula de matriz:
)
Certifique-se de substituir a URL e as referências.