Show the value on the column depending on the given value from another column

[mark692]

I want a formula that i will put on remarks column, I want it to show the name of the leader from column "Leader" depending on what team the name belong. I have a sample table below. On my example team orange has a leader James thats why on the column remarks James appear depending on the coresponding team name from column "Team". thanks

Book1
	A	B	C
1	Team	Leader	Remarks
2	Orange	James	James
3	orange		James
4	orange		James
5	Orange		James
6	Apple	Mimi	Mimi
7	Apple		Mimi
8	Apple		Mimi
9	Grapes	Larry	Larry
10	Grapes		Larry
11	Grapes		Larry
12	Grapes		Larry
Sheet1

 

[Joe4]

Are they always in order like that, with all like column A records being grouped together, and with column B always populated for the first record in each grouping?
If so, enter this formula in cell C2 and copy down for all rows:

=IF(B2<>"",B2,C1)

 

[mark692]

Are they always in order like that, with all like column A records being grouped together, and with column B always populated for the first record in each grouping?
If so, enter this formula in cell C2 and copy down for all rows:

=IF(B2<>"",B2,C1)

no sir its not always on that order. the formula that you give changes the value when i change the order sir.

 

[Joe4]

no sir its not always on that order. the formula that you give changes the value when i change the order sir.

Could you please post a more realistic example of what your data looks like then?

 

[mark692]

This is a portion of may table

on my sample column leader = Voucher No.
Team Column =Group code Column
Remarks column = Voucher No. Group

i want to put the formula on voucher no. Group thanks

Book1
	A	B	C	D	E	F	G	H
1	Check Bank	check no.	check date	amount	Voucher No.	Group Code	Voucher No. Group	Posted On
2	Metrobank	3800280392	9/29/2020	18,235.71	1 SI	1	1 SI	10/29/2020
3				164.29		1	1 SI	10/29/2020
4			11/7/2020	3,635.30		2	1 DR	10/30/2020
5			11/7/2020	1,349.78		2	1 DR	10/30/2020
6	Metrobank	3800297001	11/17/2020	38,338.97	1 DR	2	1 DR	10/30/2020
7	Metrobank	3800297002	11/18/2020	38,338.97		2	1 DR	10/30/2020
8	Metrobank	3800297003	11/19/2020	38,338.97		2	1 DR	10/30/2020
9						2	1 DR	10/30/2020
10						2	1 DR	10/30/2020
11	Metrobank	3800297004	11/24/2020	22,086.50	2 DR	3	2 DR	10/30/2020
12	Metrobank	3800297005	11/25/2020	22,086.50		3	2 DR	10/30/2020
13						3	2 DR	10/30/2020
14						3	2 DR	10/30/2020
15	Metrobank	3800297006	10/20/2020	23,454.08		4	2Si	10/30/2020
16						4	2Si	10/30/2020
17						4	2Si	10/30/2020
18			10/20/2020	211.30	2Si	4	2Si	10/30/2020
19				2,279.46	3 SI	5	3 SI	10/30/2020
20			7/25/2020	20.54		5	3 SI	10/30/2020
21	Metrobank	3800297007	10/22/2020	27,561.69	4 SI	6	4 SI	10/30/2020
22			7/24/2020	248.30		6	4 SI	10/30/2020
Sheet1

 

[Joe4]

Are we allowed to resort the data?
And are you open to a VBA solution?

 

[mark692]

yes sir you can resort the data. yes VBA is ok if there is no formula for it.

 

[Joe4]

OK, I think this should do what you want:

Sub MyMacro()

    Dim rng As Range
    Dim lr As Long
    
'   Find last row in column F with data
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    
'   Set range of data to sort
    Set rng = Range("A2").CurrentRegion
    
'   Sort data by Group Code and Voucher No. fields
    rng.Sort key1:=Range("F1"), order1:=xlAscending, _
        key2:=Range("E1"), order2:=xlAscending, Header:=xlYes
    
'   Copy data from column E to column G
    Range("E2:E" & lr).Copy Range("G2")

'   Populate missing cells with in column G with value above
    Range("G2:G" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("G2:G" & lr).Value = Range("G2:G" & lr).Value
    
End Sub

 

[mark692]

OK, I think this should do what you want:

Sub MyMacro()

    Dim rng As Range
    Dim lr As Long
   
'   Find last row in column F with data
    lr = Cells(Rows.Count, "F").End(xlUp).Row
   
'   Set range of data to sort
    Set rng = Range("A2").CurrentRegion
   
'   Sort data by Group Code and Voucher No. fields
    rng.Sort key1:=Range("F1"), order1:=xlAscending, _
        key2:=Range("E1"), order2:=xlAscending, Header:=xlYes
   
'   Copy data from column E to column G
    Range("E2:E" & lr).Copy Range("G2")

'   Populate missing cells with in column G with value above
    Range("G2:G" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("G2:G" & lr).Value = Range("G2:G" & lr).Value
   
End Sub

it works sir on the sample table i sent. ill try it now on my actual table thank you so much sir

 

[Joe4]

You are welcome.