Copy a value from column b if column contain "x"

H

Hunk1

New Member
Joined
Feb 2, 2023
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hello there,

I would like some help, i don't know if i can use formulas or if i should use VBA.

What i want is, to excel to find a the first line on the column b with name and return that name on the column j, i am thinking about the IF formula to assign a chief of the family, but the range would be dynamic regarding to lines.

suggestions?

FOLHA ASS 2022 m03 03-02-2023.xlsm
ABCDEFGHIJ
41
42168GUILHERME ERNESTO BARBOSA DE LOUREIRO706/0101/07/199609510416657FU/DCMGUILHERME ERNESTO BARBOSA DE LOUREIRO
43sem comorb.
44sem comorb.
45
46169RITA DE CASSIA BATISTA CAROLINO706/0429/08/1978700003251529301CA/SM/DC/DOMCFRITA DE CASSIA BATISTA CAROLINO
47FABIANA VIEIRA SOARES26/12/1998700906940831696PUERPERAF
48MARIA EDUARDA SOARES30/03/2011702605296732249sem comorb.F
49THIAGO SOARES DE MORAES04/04/2019708207191367642CRIM
Folha de Assinatura
Cell Formulas
RangeFormula
J42,J46J42=B42
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHIJ
41
42168GUILHERME ERNESTO BARBOSA DE LOUREIRO706/0101/07/19969510416657FU/DCMGUILHERME ERNESTO BARBOSA DE LOUREIRO
43sem comorb. 
44sem comorb. 
45 
46169RITA DE CASSIA BATISTA CAROLINO706/0429/08/19787.00003E+14CA/SM/DC/DOMCFRITA DE CASSIA BATISTA CAROLINO
47FABIANA VIEIRA SOARES26/12/19987.00907E+14PUERPERAF 
48MARIA EDUARDA SOARES30/03/20117.02605E+14sem comorb.F 
49THIAGO SOARES DE MORAES04/04/20197.08207E+14CRIM 
Main
Cell Formulas
RangeFormula
J42:J49J42=IF(AND(A42<>"",B42<>""),B42,"")
 
Upvote 0
Fluff said:
How about
Fluff.xlsm
ABCDEFGHIJ
41
42168GUILHERME ERNESTO BARBOSA DE LOUREIRO706/0101/07/19969510416657FU/DCMGUILHERME ERNESTO BARBOSA DE LOUREIRO
43sem comorb. 
44sem comorb. 
45 
46169RITA DE CASSIA BATISTA CAROLINO706/0429/08/19787.00003E+14CA/SM/DC/DOMCFRITA DE CASSIA BATISTA CAROLINO
47FABIANA VIEIRA SOARES26/12/19987.00907E+14PUERPERAF 
48MARIA EDUARDA SOARES30/03/20117.02605E+14sem comorb.F 
49THIAGO SOARES DE MORAES04/04/20197.08207E+14CRIM 
Main
Cell Formulas
RangeFormula
J42:J49J42=IF(AND(A42<>"",B42<>""),B42,"")
Click to expand...

nice, question: how hard would it be to make it self-update and fill till last set of data? i have a full table with blank lines separating the data.

like filling column J line x when new data is placed.


FOLHA ASS 2022 m03 03-02-2023.xlsm
ABCDEFGHIJ
41
42168GUILHERME ERNESTO BARBOSA DE LOUREIRO706/0101/07/199609510416657FU/DCMGUILHERME ERNESTO BARBOSA DE LOUREIRO
43sem comorb.
44sem comorb.
45
46169RITA DE CASSIA BATISTA CAROLINO706/0429/08/1978700003251529301CA/SM/DC/DOMCFRITA DE CASSIA BATISTA CAROLINO
47FABIANA VIEIRA SOARES26/12/1998700906940831696PUERPERAF
48MARIA EDUARDA SOARES30/03/2011702605296732249sem comorb.F
49THIAGO SOARES DE MORAES04/04/2019708207191367642CRIM
50
51
52211VAZIA463sem comorb.(DEPÓSITO COMERCIAL)
53
54212RONY DE MOURA561/0119/05/1984705200418546677sem comorb.M
55
56213VAZIA561/02sem comorb.
57
58214JORGE AUGUSTO AGRELO571/0119/07/1959708607079272186HA,DIAM
59HELENICE GONCALVES DE OLIVEIRA AGRELO26/03/1969700400937129543sem comorb.F
60RAFAELA AGRELO14/12/1998091.811.876-02sem comorb.F
61CHARGNER FELIPE SODRE SALES02/05/1990702409035340822sem comorb.M
62YASMIN AGRELO SALES07/08/2019179.885.086-95CRIANÇAF
63CAUA AGRELO SALES04/07/2022706103867302130CRIANÇAM
Folha de Assinatura
Cell Formulas
RangeFormula
J42,J46J42=B42
 
Upvote 0
Did you try what I suggested? You can just fill it down as far as is needed.
 
Upvote 0
Fluff said:
Did you try what I suggested? You can just fill it down as far as is needed.
Click to expand...
I did, it worked, i have another question, is it possible to make it reply the first occurance found on the next lines?

above is your formula, the section with jorge repeating is what i expected, sorry but i am a noob with excel.

Example:

FOLHA ASS 2022 m03 03-02-2023.xlsm
ABCDEFGHIJ
46169RITA DE CASSIA BATISTA CAROLINO706/0429/08/1978700003251529301CA/SM/DC/DOMCFRITA DE CASSIA BATISTA CAROLINO
47FABIANA VIEIRA SOARES26/12/1998700906940831696PUERPERAF 
48MARIA EDUARDA SOARES30/03/2011702605296732249sem comorb.F 
49THIAGO SOARES DE MORAES04/04/2019708207191367642CRIM 
50 
51 
52211VAZIA463sem comorb.(DEPÓSITO COMERCIAL)VAZIA
53 
54212RONY DE MOURA561/0119/05/1984705200418546677sem comorb.MRONY DE MOURA
55 
56213VAZIA561/02sem comorb.VAZIA
57 
58214JORGE AUGUSTO AGRELO571/0119/07/1959708607079272186HA,DIAMJORGE AUGUSTO AGRELO
59HELENICE GONCALVES DE OLIVEIRA AGRELO26/03/1969700400937129543sem comorb.FJORGE AUGUSTO AGRELO
60RAFAELA AGRELO14/12/1998091.811.876-02sem comorb.FJORGE AUGUSTO AGRELO
61CHARGNER FELIPE SODRE SALES02/05/1990702409035340822sem comorb.MJORGE AUGUSTO AGRELO
62YASMIN AGRELO SALES07/08/2019179.885.086-95CRIANÇAFJORGE AUGUSTO AGRELO
63CAUA AGRELO SALES04/07/2022706103867302130CRIANÇAMJORGE AUGUSTO AGRELO
Folha de Assinatura
Cell Formulas
RangeFormula
J46:J58J46=IF(AND(A46<>"",B46<>""),B46,"")
 
Upvote 0
How about
Excel Formula: 
=IF(AND(A42<>"",B42<>""),B42,IF(B42<>"",J41,""))
 
Upvote 1
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
I would like to ask another help which is similar to this, now with address, I want excel to search up a address and fill up the numbers according to people living on the house, however i crashed trying to do the formula i stopped at:
Excel Formula: 
=IF(AND(OR(COUNT.IF('Folha de Assinatura'!C10;"RUA*")=1;(COUNT.IF('Folha de Assinatura'!C10;"AVENIDA*")=1));SE('Folha de Assinatura'!C10<>""'Folha de Assinatura'!C10&" , "&'Folha de Assinatura'!C12;"")

The reference to the adress is on the column C

any idea of how to do that without assemblying a lot of IF functions with count ifs?

This is the data sheet:
FOLHA ASS 2022 m03 03-02-2023.xlsm
ABCDEFGH
10RUA NATERCIA 37504080
11FANOMENºD. NASC.CARTÃO DO SUSDOENÇA DATA VDASSINATURA
12159BENEDITO GONCALVES FILHO9010/05/1951702103775242996HA/DIA*
13
14160TERESINHA DE FATIMA DA SILVA 6001/04/1973708504337827477FU
15RICARDO MARCOS DE ARAUJO18/06/1971772.478.686-00HA/FU
16FILIPE MARCOS ARAUJO02/07/1993117.995.396-79FU
17
Folha de Assinatura



The result:
FOLHA ASS 2022 m03 03-02-2023.xlsm
BCDEFGH
9NOMED. NASC.ANO NASCCONDIÇÃO SEXOQuem é o Chefe?ENDEREÇO
10BENEDITO GONCALVES FILHO10/05/19511951HA/DIA*MBENEDITO GONCALVES FILHORUA NATERCIA 37504080 , 90
1100000 
12TERESINHA DE FATIMA DA SILVA 01/04/19731973FUFTERESINHA DE FATIMA DA SILVA RUA NATERCIA 37504080 , 60
13RICARDO MARCOS DE ARAUJO18/06/19711971HA/FUMTERESINHA DE FATIMA DA SILVA RUA NATERCIA 37504080 ,
14FILIPE MARCOS ARAUJO02/07/19931993FUMTERESINHA DE FATIMA DA SILVA RUA NATERCIA 37504080 ,
Planilha para Roteiro
Cell Formulas
RangeFormula
B10:B14B10='Folha de Assinatura'!B12
C10:C14,E10:E14C10='Folha de Assinatura'!D12
D10:D14D10=RIGHT(C10,4)
F10:G14F10='Folha de Assinatura'!I12
H10H10=IF(AND(COUNTIF('Folha de Assinatura'!C10,"RUA*")),'Folha de Assinatura'!C10&" , "&'Folha de Assinatura'!C12,"")
H12:H14H12=IF(AND(A12<>"",B12<>""),'Folha de Assinatura'!$C$10&" , " &'Folha de Assinatura'!C14,IF(B12<>"",'Folha de Assinatura'!C12&" , " &'Folha de Assinatura'!C14,""))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E10:E1089Cell Value="SEM DOEN."textNO
D10:D1089Cell Valuebetween 2004 and 2023textNO
F9:G9Cell Valuecontains "f"textNO
F9:G9Cell Valuecontains "M"textNO
E9:E1089Celldoes not contain a blank value textNO
B10:B1089Cell ValueduplicatestextNO
B10:B1089Cell ValueduplicatestextNO
B10:B1089Cell ValueduplicatestextNO



and the expected auto-fill:
FOLHA ASS 2022 m03 03-02-2023.xlsm
BCDEFGHI
9NOMED. NASC.ANO NASCCONDIÇÃO SEXOQuem é o Chefe?ENDEREÇOCARTÃO DO SUS
10BENEDITO GONCALVES FILHO10/05/19511951HA/DIA*MBENEDITO GONCALVES FILHORUA NATERCIA 37504080 , 90702103775242996
1100000 0
12TERESINHA DE FATIMA DA SILVA 01/04/19731973FUFTERESINHA DE FATIMA DA SILVA RUA NATERCIA 37504080 , 60708504337827477
13RICARDO MARCOS DE ARAUJO18/06/19711971HA/FUMTERESINHA DE FATIMA DA SILVA RUA NATERCIA 37504080 , 60772.478.686-00
14FILIPE MARCOS ARAUJO02/07/19931993FUMTERESINHA DE FATIMA DA SILVA RUA NATERCIA 37504080 , 117.995.396-79
Planilha para Roteiro
Cell Formulas
RangeFormula
B10:B14B10='Folha de Assinatura'!B12
C10:C14,E10:E14C10='Folha de Assinatura'!D12
D10:D14D10=RIGHT(C10,4)
F10:G14F10='Folha de Assinatura'!I12
H10H10=IF(AND(COUNTIF('Folha de Assinatura'!C10,"RUA*")),'Folha de Assinatura'!C10&" , "&'Folha de Assinatura'!C12,"")
H12,H14H12=IF(AND(A12<>"",B12<>""),'Folha de Assinatura'!$C$10&" , " &'Folha de Assinatura'!C14,IF(B12<>"",'Folha de Assinatura'!C12&" , " &'Folha de Assinatura'!C14,""))
I10:I14I10='Folha de Assinatura'!E12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E10:E1089Cell Value="SEM DOEN."textNO
D10:D1089Cell Valuebetween 2004 and 2023textNO
F9:G9Cell Valuecontains "f"textNO
F9:G9Cell Valuecontains "M"textNO
E9:E1089Celldoes not contain a blank value textNO
B10:B1089Cell ValueduplicatestextNO
B10:B1089Cell ValueduplicatestextNO
B10:B1089Cell ValueduplicatestextNO
 
Upvote 0
As this is a totally different question, it needs a new thread. Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Auto-filling the address column with info from data sheet
Replies
1
Views
250
sijpie
sijpie
Z
  • Solved
Count Distinct Values in Filtered Table across Multiple Columns
Replies
6
Views
363
Fluff
Fluff
Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
179
zero269
Z
M
  • Question
Pivot Table help - Can I show certain data depending on value in another field
Replies
2
Views
255
Monty85
M
R
  • Solved
Multi Dim array printing specific value to range depending on value in Col A
Replies
3
Views
397
Alex Blakenburg
A

Forum statistics

Threads
1,214,834
Messages
6,121,877
Members
449,056
Latest member
ruhulaminappu

Share this page

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
Back
Top