Getting values from one line if cells in another line are not empty

cogumelo

Board Regular
Joined
Mar 23, 2006
Messages
181
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I'm trying to build a concatenation for each line (group 1, group 2, group 3,...) based on the values each line has.

For example, for Group 1 line, if a cell value is not blank then i wish to concatenate the country and category for that column. So for Group the concatenation should be PT1100. For Group 2 line, it should be PT1101 + ES1100 + ES1101 and for Group 3 line it should be PT1100 + ES1101

Country -->​
PTPTESES
Category -->​
1100 Cat 11101 Cat 21100 Cat 11101 Cat 2Concatenation
Group 1100-1PT1100
Group 2202023PT1101 + ES1100 + ES1101
Group 33021PT1100 + ES1101
Group 4

Can it be done? Thx in advance for any help.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try.
F3
=TEXTJOIN("+",TRUE,IF(B3:E3<>"",B$1:E$1&LEFT(B$2:E$2,4),""))
Press CTRL+SHIFT+ENTER to enter array formulas.
Copy F3 downward.
book1
ABCDEF
1Country -->PTPTESES
2Category -->1100 Cat 11101 Cat 21100 Cat 11101 Cat 2Concatenation
3Group 1100PT1100
4Group 2202023PT1101+ES1100+ES1101
5Group 33021PT1100+ES1101
sheet1
Cell Formulas
RangeFormula
F3:F5F3=TEXTJOIN("+",TRUE,IF(B3:E3<>"",B$1:E$1&LEFT(B$2:E$2,4),""))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks a lot! Worked like a charm.
(confirmed no need for CTRL+SHIFT+ENTER as I'm using M365)
 
Upvote 0
Thanks a lot! Worked like a charm.
(confirmed no need for CTRL+SHIFT+ENTER as I'm using M365)
Just checking:
if a cell value is not blank then i wish to concatenate the country and category for that column.
.. so why isn't the first result as below, given the the first row contains "100" and "-1", both of which meet the <>"" condition?
PT1100 + ES1100

Either that first expected result is incorrect or else you actually need something more like this?
Excel Formula:
=TEXTJOIN("+",1,IF(B3:E3>0,B$1:E$1&LEFT(B$2:E$2,4),""))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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