Question on Merging of rows

[Wee001]

Hi, I have a sheet of over 5000 rows, and I'm looking to merge rows with a similar name, while combining the rows so that main row contains all the necessary information. Not really sure how to explain it so decide to try and describe it with an example. Would greatly appreciate any help I could get.
So my sheet is of this format, 

First Name	Last name	Address	Phone	Email	Consent	Supporting Docs
Ben	Lee	TRUE
Ben	Lee		TRUE
Ben	Lee			TRUE
Jon	Wee		TRUE
Jon	Wee				TRUE
Roy	Ho					TRUE
Roy	Ho			TRUE
Roy	Ho	TRUE

And I'm trying to make it so that it looks like this:

First Name	Last name	Address	Phone	Email	Consent	Supporting Docs
Ben	Lee	TRUE	TRUE	TRUE
Jon	Wee		TRUE		TRUE
Roy	Ho	TRUE		TRUE		TRUE

And also, would it be possible to fill up the blank cells with the word "FALSE" as per below example?

First Name	Last name	Address	Phone	Email	Consent	Supporting Docs
Ben	Lee	TRUE	TRUE	TRUE	FALSE	FALSE
Jon	Wee	FALSE	TRUE	FALSE	TRUE	FALSE
Roy	Ho	TRUE	FALSE	TRUE	FALSE	TRUE

[#2]

@Wee001 

You can achieve the desired result by using Excel's Power Query feature.

Here's how you can do it:

Assuming your data is in columns A to G and your headers are in row 1, you can follow these steps:

1. Select a cell within your data range.
2. Go to the "Data" tab on the Excel ribbon.
3. In the "Get & Transform Data" group, select "Get Data" and then choose "From Table/Range." This will open the Power Query Editor.
4. In the Power Query Editor, you will see your data in a tabular format.
5. In the "First Name" column, click the dropdown arrow and select "Fill Down." This will fill down the blank cells with the values from the previous row.
6. Select all the columns (First Name to Supporting Docs).
7. Go to the "Transform" tab and choose "Pivot Column."
8. In the Pivot Column dialog box, select "Consent" as the "Values Column."
9. In the "Advanced Options," you can choose to aggregate values with "Don't Aggregate."
10. Click OK, and your data will be transformed to look like the desired format.
11. To replace the remaining blank cells with "FALSE," select all columns, then go to the "Replace Values" option under the "Home" tab. Replace any blank values with "FALSE."
12. Finally, close and load your data back into Excel.

This will give you the merged rows with "TRUE" and "FALSE" values in the desired format.

Please note that Power Query is available in newer versions of Excel, if you have an older version of Excel, you may need to install the Power Query add-in separately.

If you prefer not to use Power Query, you can achieve the desired result using Excel formulas, but it may involve some manual work. Here's a method using formulas:

Assuming your data starts in cell A1, you can place the following formulas in your destination worksheet:

In cell A2, enter the formula for the "First Name" column and drag it down:

=IF(A1<>A2, A2, "")

In cell B2, enter the formula for the "Last Name" column and drag it down:

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

For the "Address," "Phone," "Email," "Consent," and "Supporting Docs" columns, you can use the MAX function to combine the values for each person:

In cell C2 (for "Address"), enter the following formula and drag it down:

=MAX(IF($A$2:$A$5000=A2, $C$2:$C$5000, 0))

Repeat the same formula for the "Phone," "Email," "Consent," and "Supporting Docs" columns, changing the column reference accordingly.

Now, your data should be in the desired format, and blank cells will contain 0s for the columns with "FALSE" values. You can use the Find and Replace function to replace the 0s with "FALSE."

1. Select the entire worksheet or the columns where you want to replace 0s.
2. Press Ctrl + H to open the Find and Replace dialog.
3. In the "Find what" field, enter 0.
4. In the "Replace with" field, enter FALSE.
5. Click "Replace All."

This will replace the 0s with "FALSE" as per your request.

While this method doesn't automate the process as effectively as Power Query, it provides a formula-based solution without using Power Query.

The text was created with the help of AI.

Additionally, please ensure that your Excel is up-to-date with the latest updates from Microsoft.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

Hello @Wee001

I think you can do it using excel formula too. However, we may need to add 1 Extra column. Will that be fine ?

[#4]

@Wee001 

=IF(COUNTIFS($A$2:$A$9,$I2,$B$2:$B$9,$J2,C$2:C$9,"TRUE"),"TRUE","FALSE")

An alternative could be this formula. The formula is in cell K2 and filled across range K2:O4. The unique entries of first name and last name in columns I and J were done with Data -> Remove duplicates.

[#5]

@Wee001 

=LET(rng,A2:G13,HSTACK(UNIQUE(TAKE(rng,,2)),IF(DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(TAKE(rng,,2)))),LAMBDA(u,v,VSTACK(u,TOROW(DROP(REDUCE("",SEQUENCE(,5),LAMBDA(x,y,VSTACK(x,COUNTIFS(TAKE(rng,,1),INDEX(UNIQUE(TAKE(rng,,2)),v,1),TAKE(TAKE(rng,,2),,-1),INDEX(UNIQUE(TAKE(rng,,2)),v,2),INDEX(DROP(rng,,2),,y),"TRUE")))),1))))),1),"TRUE","FALSE")))

An alternative with Office 365 or Excel for the web could be this formula which spills the results.

[#6]

=webservice("https://e.anyoupin.cn/EData/?v=111&q=cli_tb~sht~"&a17&b19

@Wee001 

a17=

ARRAYTOTEXT(A2:G9,1)

a19=

=webservice("https://e.anyoupin.cn/EData/?v=111&q=cli_tb~sht~"&a17&select f01,f02,colIdxf[2:]{iif(cast(group_concat(%s,'') as int)=1,"TRUE","FALSE")} from sht group by f01,f02

a21=TEXTSPLIT(A19,"□","△")

 

view-source:https://e.anyoupin.cn/EData/?v=111&q=cli_tb~sht~{%22Ben%22,%22Lee%22,TRUE,%22%C2%A0%22,%22%C2%A0%22,...

[#7]

a19=
webservice("

cli_tb~sht~{"Ben","Lee",TRUE," "," "," "," ";"Ben","Lee"," ",TRUE," "," "," ";"Ben","Lee"," "," ",TRUE," "," ";"Jon","Wee"," ",TRUE," "," "," ";"Jon","Wee"," "," "," ",TRUE," ";"Roy","Ho"," "," "," "," ",TRUE;"Roy","Ho"," "," ",TRUE," "," ";"Roy","Ho",TRUE," "," "," "," "}
;
select f01,f02,colIdxf[2:]{group_concat(%s,'-')} from sht group by f01,f02;
select f01,f02,colIdxf[2:]{iif(instr(group_concat(%s,'-'),'1'),'TRUE','FALSE')} from sht group by f01,f02")

[#8]

@Wee001 

A 365 solution with the source data tabled:

=LET(
    header, Table1[#Headers],
    fullname, Table1[First Name] & " " & Table1[Last name],
    names, SORT(UNIQUE(fullname)),
    Consolidate, LAMBDA(a, v,
        LET(
            filtered, N(
                FILTER(Table1[[Address]:[Supporting Docs]], fullname = v)
            ),
            TF_array, BYCOL(
                filtered,
                LAMBDA(col, IF(SUM(col) >= 1, TRUE, FALSE))
            ),
            splitname, TEXTSPLIT(v, " "),
            VSTACK(a, HSTACK(splitname, TF_array))
        )
    ),
    REDUCE(header, names, Consolidate)
)