Formula for copying value from a cell if another cell contains a specific string

M

mishath

New Member
Joined
Oct 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a column that has around 500 cells and in each cell has one of these 3 values - SW_A, SW_B, and Light

1633109068857.png


Now I want to create a column that will have the value of the start column in even number cells and the value of the stop column in odd number cells if the component is LIGHT.

Like this:

1633109297162.png


Is there anyway to automate this with a formula?

Thank you
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Yes. But How you have 2:04:00 at result but, I cann't see it at source cells.
 
Upvote 0
How about:
Book1.xlsx
ABCDEFG
1StartStopComponentComponentTime
214:04:1014:04:12SW-CLight14:04:10
314:04:1514:04:17LightLight14:04:17
414:04:2014:04:22SW-CLight14:04:20
514:04:2514:04:27LightLight14:04:27
614:04:3014:04:32SW-CLight14:04:30
714:04:3514:04:37LightLight14:04:37
814:04:4014:04:42SW-CLight14:04:40
914:04:4514:04:47LightLight14:04:47
1014:04:5014:04:52SW-CLight14:04:50
1114:04:5514:04:57LightLight14:04:57
1214:05:0014:05:02SW-CLight14:05:00
1314:05:0514:05:07LightLight14:05:07
1414:05:1014:05:12SW-CLight14:05:10
1514:05:1514:05:17LightLight14:05:17
1614:05:2014:05:22SW-CLight14:05:20
1714:05:2514:05:27LightLight14:05:27
1814:05:3014:05:32SW-CLight14:05:30
1914:05:3514:05:37LightLight14:05:37
2014:05:4014:05:42SW-CLight14:05:40
2114:05:4514:05:47LightLight14:05:47
2214:05:5014:05:52SW-CLight14:05:50
2314:05:5514:05:57LightLight14:05:57
2414:06:0014:06:02SW-CLight14:06:00
2514:06:0514:06:07LightLight14:06:07
2614:06:1014:06:12SW-CLight14:06:10
2714:06:1514:06:17LightLight14:06:17
2814:06:2014:06:22SW-CLight14:06:20
2914:06:2514:06:27LightLight14:06:27
3014:06:3014:06:32SW-CLight14:06:30
3114:06:3514:06:37LightLight14:06:37
3214:06:4014:06:42SW-CLight14:06:40
3314:06:4514:06:47LightLight14:06:47
3414:06:5014:06:52SW-CLight14:06:50
3514:06:5514:06:57LightLight14:06:57
3614:07:0014:07:02SW-CLight14:07:00
3714:07:0514:07:07LightLight14:07:07
3814:07:1014:07:12SW-CLight14:07:10
3914:07:1514:07:17LightLight14:07:17
4014:07:2014:07:22SW-CLight14:07:20
4114:07:2514:07:27LightLight14:07:27
4214:07:3014:07:32SW-CLight14:07:30
43
Sheet2
Cell Formulas
RangeFormula
F2:F42F2=IF(MOD(ROW(),2)=0,A2,B2)
 
Upvote 0
maabadi said:
How about:
Book1.xlsx
ABCDEFG
1StartStopComponentComponentTime
214:04:1014:04:12SW-CLight14:04:10
314:04:1514:04:17LightLight14:04:17
414:04:2014:04:22SW-CLight14:04:20
514:04:2514:04:27LightLight14:04:27
614:04:3014:04:32SW-CLight14:04:30
714:04:3514:04:37LightLight14:04:37
814:04:4014:04:42SW-CLight14:04:40
914:04:4514:04:47LightLight14:04:47
1014:04:5014:04:52SW-CLight14:04:50
1114:04:5514:04:57LightLight14:04:57
1214:05:0014:05:02SW-CLight14:05:00
1314:05:0514:05:07LightLight14:05:07
1414:05:1014:05:12SW-CLight14:05:10
1514:05:1514:05:17LightLight14:05:17
1614:05:2014:05:22SW-CLight14:05:20
1714:05:2514:05:27LightLight14:05:27
1814:05:3014:05:32SW-CLight14:05:30
1914:05:3514:05:37LightLight14:05:37
2014:05:4014:05:42SW-CLight14:05:40
2114:05:4514:05:47LightLight14:05:47
2214:05:5014:05:52SW-CLight14:05:50
2314:05:5514:05:57LightLight14:05:57
2414:06:0014:06:02SW-CLight14:06:00
2514:06:0514:06:07LightLight14:06:07
2614:06:1014:06:12SW-CLight14:06:10
2714:06:1514:06:17LightLight14:06:17
2814:06:2014:06:22SW-CLight14:06:20
2914:06:2514:06:27LightLight14:06:27
3014:06:3014:06:32SW-CLight14:06:30
3114:06:3514:06:37LightLight14:06:37
3214:06:4014:06:42SW-CLight14:06:40
3314:06:4514:06:47LightLight14:06:47
3414:06:5014:06:52SW-CLight14:06:50
3514:06:5514:06:57LightLight14:06:57
3614:07:0014:07:02SW-CLight14:07:00
3714:07:0514:07:07LightLight14:07:07
3814:07:1014:07:12SW-CLight14:07:10
3914:07:1514:07:17LightLight14:07:17
4014:07:2014:07:22SW-CLight14:07:20
4114:07:2514:07:27LightLight14:07:27
4214:07:3014:07:32SW-CLight14:07:30
43
Sheet2
Cell Formulas
RangeFormula
F2:F42F2=IF(MOD(ROW(),2)=0,A2,B2)
Click to expand...
thanks a lot but this only works for this table right? and there are some situations where Light comes up twice or thrice in a row, will it work in conditions like that?
 
Upvote 0
Please upload example with exact structure of your data and Describe exactly with details what yo want.
 
Upvote 0
This is just an attempt... but I think that's the way to go (with MATCH and INDEX).
I can do more if you provide me you full data.

MrExcel_mishath_column_light.xlsx
ABCDEF
1StartStopComponentComponentTime
21:50:36 PM1:50:46 PMSW_CLIGHT4:58:14 AM
38:10:35 PM8:10:49 PMSW_ALIGHT4:58:59 AM
41:00:07 PM1:00:55 PMSW_BLIGHT3:24:32 AM
51:48:20 AM1:48:34 AMSW_CLIGHT3:24:55 AM
64:58:14 AM4:58:59 AMLIGHTLIGHT5:55:07 PM
71:01:03 PM1:01:06 PMSW_BLIGHT5:55:32 PM
83:24:32 AM3:24:55 AMSW_BLIGHT11:29:14 AM
92:55:17 AM2:55:58 AMSW_CLIGHT11:30:14 AM
105:55:07 PM5:55:32 PMSW_ALIGHT6:00:03 PM
112:46:52 AM2:47:26 AMSW_BLIGHT6:00:14 PM
1211:29:14 AM11:30:14 AMLIGHTLIGHT8:14:06 AM
1310:33:51 AM10:34:50 AMSW_CLIGHT8:14:41 AM
146:00:03 PM6:00:14 PMSW_CLIGHT11:16:35 PM
154:24:46 PM4:24:56 PMSW_BLIGHT11:16:59 PM
168:14:06 AM8:14:41 AMLIGHTLIGHT7:45:35 AM
174:39:39 PM4:39:46 PMSW_BLIGHT7:46:29 AM
1811:16:35 PM11:16:59 PMSW_CLIGHT12:00:00 AM
194:40:16 PM4:40:47 PMLIGHTLIGHT12:00:00 AM
207:45:35 AM7:46:29 AMSW_ALIGHT12:00:00 AM
215:20:04 PM5:20:07 PMSW_ALIGHT12:00:00 AM
22……
Sheet1
Cell Formulas
RangeFormula
A2:A21A2= RAND()
B2:B21B2= TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) + TIME(0, 0, RANDBETWEEN(1,60))
C2:C21C2=IF(RANDBETWEEN(1,3)=1, "SW_A", IF(RANDBETWEEN(1,3)=2, "SW_B", IF(RANDBETWEEN(1,3)=3, "SW_C", "LIGHT")))
F2,F4,F6,F8,F10,F12,F14,F16,F18,F20F2= INDEX(A2:C21,MATCH("LIGHT",$C$2:$C$21,0),1)
F3,F5,F7,F9,F11,F13,F15,F17,F19,F21F3= INDEX(A2:C21,MATCH("LIGHT",$C$2:$C$21,0),2)


Don't mind the Formulas in Cells A2, B2 and C2. That's just randomly generated time and text, because I was too lazy to type in some sample data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Formula Evaluation Issue
Replies
4
Views
226
zero269
Z
VinceF
  • Solved
Copy/Paste Value Only If The Cell Contains A Number
Replies
9
Views
179
smozgur
smozgur
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
250
Gajendran Yadhav
G
A
  • Question
Find value from one workbook in another
Replies
4
Views
236
AndyEd
A
A
  • Question
Lookup value based on multiple criteria where one is searching a cell for specific text string
Replies
0
Views
228
alex0182828
A

Forum statistics

Threads
1,214,523
Messages
6,120,033
Members
448,940
Latest member
mdusw

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