Combine Column Cells If Other Col Cells Equal One Another

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
598
I need to combine column cells from col D if the row cell values in cols F, G, H are equal. Here is a picture of part of the sheet.

Excel Workbook
DEFGH
1TopicBkNameBkNumberChapNumberVerseNumber
2Vanity --- All earthly things areEccl2112
3Emptiness --- Emptiness Or Vanity Of The Worldly Life --- (Ps 39.11\, 62.9)Eccl2112
4Vanity --- Emptiness Of The Worldly Life --- (Ps 39:11\, 62:9)Eccl2112
5Business Life --- Capital And Labor --- Labor --- Worldly\, Sometimes DisappointingEccl2113
6Capital and Labor --- Labor --- Worldly\, Sometimes DisappointingEccl2113
7Fruitless Labor --- (Worldly\, Sometimes Disappointing)Eccl2113
8Toil --- Worldly\, Sometimes DisappointingEccl2113
9Worldly --- Labor --- Worldly\, Sometimes DisappointingEccl2113
10Sun --- General References ToEccl2115
11Wind --- Variable nature ofEccl2116
12River --- Run into the seaEccl2117
13Sea --- Rivers supplied by exhalations fromEccl2117
14Sea --- Replenished by riversEccl2117
15Water --- Rises in vapor to the cloudsEccl2117
16Ear --- Not satisfied with earthly thingsEccl2118
17Eye --- Not satisfied with seeingEccl2118
18Emptiness --- Men Unsatisfied\, With Temporal Things --- Opportunities For Seeing And HearingEccl2118
19Unsatisfied --- Opportunities For Seeing And HearingEccl2118
Eccl_John


F2, G2, H2 values = F3, G3, H3 values and so on down the sheet. I want to combine the cell values for D2, D3, and so on down the sheet as long as the column values F, G, H in each row are equal. I want to put the combined cell values into col I right before the cell row values change. In the example shown, D2, D3, D4 would be combined and appear in I4.

Likewise D5, D6, D7, D8, and D9 would be combined and appear in I9...because of the equal values in the cells in F5, F6, F7, F8, and F9

Likewise D12, D13, D14, and D15 would be combined and appear in I15...because of the equal values in the cells in F12, F13, F14, and F15


Thank you in advance for your replies.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
I need to combine column cells from col D if the row cell values in cols F, G, H are equal. Here is a picture of part of the sheet.

Excel Workbook
DEFGH
1TopicBkNameBkNumberChapNumberVerseNumber
2Vanity --- All earthly things areEccl2112
3Emptiness --- Emptiness Or Vanity Of The Worldly Life --- (Ps 39.11\, 62.9)Eccl2112
4Vanity --- Emptiness Of The Worldly Life --- (Ps 39:11\, 62:9)Eccl2112
5Business Life --- Capital And Labor --- Labor --- Worldly\, Sometimes DisappointingEccl2113
6Capital and Labor --- Labor --- Worldly\, Sometimes DisappointingEccl2113
7Fruitless Labor --- (Worldly\, Sometimes Disappointing)Eccl2113
8Toil --- Worldly\, Sometimes DisappointingEccl2113
9Worldly --- Labor --- Worldly\, Sometimes DisappointingEccl2113
10Sun --- General References ToEccl2115
11Wind --- Variable nature ofEccl2116
12River --- Run into the seaEccl2117
13Sea --- Rivers supplied by exhalations fromEccl2117
14Sea --- Replenished by riversEccl2117
15Water --- Rises in vapor to the cloudsEccl2117
16Ear --- Not satisfied with earthly thingsEccl2118
17Eye --- Not satisfied with seeingEccl2118
18Emptiness --- Men Unsatisfied\, With Temporal Things --- Opportunities For Seeing And HearingEccl2118
19Unsatisfied --- Opportunities For Seeing And HearingEccl2118
Eccl_John


F2, G2, H2 values = F3, G3, H3 values and so on down the sheet. I want to combine the cell values for D2, D3, and so on down the sheet as long as the column values F, G, H in each row are equal. I want to put the combined cell values into col I right before the cell row values change. In the example shown, D2, D3, D4 would be combined and appear in I4.

Likewise D5, D6, D7, D8, and D9 would be combined and appear in I9...because of the equal values in the cells in F5, F6, F7, F8, and F9

Likewise D12, D13, D14, and D15 would be combined and appear in I15...because of the equal values in the cells in F12, F13, F14, and F15


Thank you in advance for your replies.

Maybe:

In Column I copied down.

=IF(AND(F2=F3,G2=G3,H2=H3),CONCATENATE(D2,D3),"")
 

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
598
Thank you. The formula concatenates only two cells. I need to concatenate a varied number of cells, depending on how many cells in F, G, and H equal one another. For example, after an appropriate macro or formula is run, I4 would contain the values in F2, F3, and F4.
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Thank you. The formula concatenates only two cells. I need to concatenate a varied number of cells, depending on how many cells in F, G, and H equal one another. For example, after an appropriate macro or formula is run, I4 would contain the values in F2, F3, and F4.

Sorry that didn't do the trick. This is a bit out of my range, maybe others can help you.
 

HowdeeDoodee

Well-known Member
Joined
Nov 15, 2004
Messages
598
Thank you for the honest reply. I ended up using Word macros to solve the issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,987
Messages
5,656,249
Members
418,292
Latest member
spd87

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
Top