1 or 0 based on Column Reference

[SOLTEC]

I have spreadsheet and I need to number default images with a 1 and all other with a 0(zero). The Column F has the values from 1:1 through 7000+:1. Any cell with a :1 is a default image and needs a 1 in the M Column. Any other number than :1 is a 0 (zero). Thank you for any and all assistance, it is greatly appreciated.

Here is the Spreadsheet cut

Excel 2010
	A	F	G	H	M
1	ID	Value(Value:Position)	Supplier #	Reference #	Default (0/1)
2	1	1:1	10-01	1001	1
3	1	1:2	10-012	10012	0
4	2	2:1	10-50	1050	1
5	2	2:2	10-502	10502	0
6	2	2:3	10-505	10505	0
7	2	2:4	10-507	10507	0
8	3	3:1	10-89 04 W	1089	1
9	3	3:2	10-89 05 W	1089	0
10	3	3:3	10-89 08 R	1089	0
11	3	3:4	10-89 11 W	1089	0
12	3	3:5	10-89 14 R	1089	0
13	3	3:6	10-89 14 W	1089	0
14	4	4:1	11-10	1110	1
15	4	4:2	11-11	1111	0
16	4	4:3	11-14	1114	0
17	4	4:4	11-143	11143	0
18	4	4:5	11-147	11147	0
19	4	4:6	11-15	1115	0
20	4	4:7	11-16	1116	0
21	5	5:1	12-01	1201	1
22	6	6:1	12-01F	1201F	1
23	5	5:2	12-02	1202	0
24	5	5:3	12-025	12025	0
25	5	5:4	12-027	12027	0
26	5	5:5	12-028	12028	0
27	7	7:1	12-02F	1202F	1
28	5	5:6	12-03	1203	0
29	8	8:1	12-031F	12031F	1
30	9	9:1	12-032F	12032F	1
31	10	10:1	12-033F	12033F	1
32	5	5:7	12-035	12035	0
33	11	11:1	12-03F	1203F	0
34	5	5:8	12-04	1204	0
35	8	8:2	12-041F	12041F	0
36	9	9:2	12-042F	12042F	0
37	10	10:2	12-043F	12043F	0
38	11	11:2	12-04F	1204F	0
39	5	5:9	12-05	1205	0
40	5	5:10	12-055	12055	0
41	12	12:1	12-06	1206	1
42	5	5:11	12-07	1207	0
43	13	13:1	12-08	1208	0
Fox Combinations

Cell Formulas
Range	Formula
A2	1
A3	1
A4	2
A5	2
A6	2
A7	2
A8	3
A9	3
A10	3
A11	3
A12	3
A13	3
A14	4
A15	4
A16	4
A17	4
A18	4
A19	4
A20	4
A21	5
A22	6
A23	5
A24	5
A25	5
A26	5
A27	7
A28	5
A29	8
A30	9
A31	10
A32	5
A33	11
A34	5
A35	8
A36	9
A37	10
A38	11
A39	5
A40	5
A41	12
A42	5
A43	13
B2	Replacement Boot Laces
B3	Replacement Boot Laces
B4	Blousing Garters
B5	Blousing Garters
B6	Blousing Garters
B7	Blousing Garters
B8	GI Extreme Cold Weather Bunny Boots
B9	GI Extreme Cold Weather Bunny Boots
B10	GI Extreme Cold Weather Bunny Boots
B11	GI Extreme Cold Weather Bunny Boots
B12	GI Extreme Cold Weather Bunny Boots
B13	GI Extreme Cold Weather Bunny Boots
B14	Nylon Commando Wallet
B15	Nylon Commando Wallet
B16	Nylon Commando Wallet
B17	Nylon Commando Wallet
B18	Nylon Commando Wallet
B19	Nylon Commando Wallet
B20	Nylon Commando Wallet
B21	Pepper Sprays Gel Sabre Red
B22	Fire Master Fog Pepper Spray
B23	Pepper Sprays Gel Sabre Red
B24	Pepper Sprays Gel Sabre Red
B25	Pepper Sprays Gel Sabre Red
B26	Pepper Sprays Gel Sabre Red
B27	Key Chain Pepper Spray
B28	Pepper Sprays Gel Sabre Red
B29	Cop Top Fog Cone Pepper Spray
B30	Flip Top Stream Pepper Spray
B31	Flip Top Cop Med Fog Cone Pepper Spray
B32	Pepper Sprays Gel Sabre Red
B33	Cop Top Stream Pepper Spray
B34	Pepper Sprays Gel Sabre Red
B35	Cop Top Fog Cone Pepper Spray
B36	Flip Top Stream Pepper Spray
B37	Flip Top Cop Med Fog Cone Pepper Spray
B38	Cop Top Stream Pepper Spray
B39	Pepper Sprays Gel Sabre Red
B40	Pepper Sprays Gel Sabre Red
B41	Police Magnum Sabre Red
B42	Pepper Sprays Gel Sabre Red
B43	Police Magnum Advanced 3 in 1 Formaula Sabre Red
C2	Black
C3	Sand
C4	Olive Drab
C5	Khaki
C6	Black
C7	Foliage Green
C14	Olive Drab
C15	Black
C16	Woodland Camo
C17	Digital Woodland
C18	Terrain Digital
C19	US Army Logo
C20	USMC Logo
F2	1:1
F3	1:2
F4	2:1
F5	2:2
F6	2:3
F7	2:4
F8	3:1
F9	3:2
F10	3:3
F11	3:4
F12	3:5
F13	3:6
F14	4:1
F15	4:2
F16	4:3
F17	4:4
F18	4:5
F19	4:6
F20	4:7
F21	5:1
F22	6:1
F23	5:2
F24	5:3
F25	5:4
F26	5:5
F27	7:1
F28	5:6
F29	8:1
F30	9:1
F31	10:1
F32	5:7
F33	11:1
F34	5:8
F35	8:2
F36	9:2
F37	10:2
F38	11:2
F39	5:9
F40	5:10
F41	12:1
F42	5:11
F43	13:1
G2	10-01
G3	10-012
G4	10-50
G5	10-502
G6	10-505
G7	10-507
G8	10-89 04 W
G9	10-89 05 W
G10	10-89 08 R
G11	10-89 11 W
G12	10-89 14 R
G13	10-89 14 W
G14	11-10
G15	11-11
G16	11-14
G17	11-143
G18	11-147
G19	11-15
G20	11-16
G21	12-01
G22	12-01F
G23	12-02
G24	12-025
G25	12-027
G26	12-028
G27	12-02F
G28	12-03
G29	12-031F
G30	12-032F
G31	12-033F
G32	12-035
G33	12-03F
G34	12-04
G35	12-041F
G36	12-042F
G37	12-043F
G38	12-04F
G39	12-05
G40	12-055
G41	12-06
G42	12-07
G43	12-08
H2	1001
H3	10012
H4	1050
H5	10502
H6	10505
H7	10507
H8	1089
H9	1089
H10	1089
H11	1089
H12	1089
H13	1089
H14	1110
H15	1111
H16	1114
H17	11143
H18	11147
H19	1115
H20	1116
H21	1201
H22	1201F
H23	1202
H24	12025
H25	12027
H26	12028
H27	1202F
H28	1203
H29	12031F
H30	12032F
H31	12033F
H32	12035
H33	1203F
H34	1204
H35	12041F
H36	12042F
H37	12043F
H38	1204F
H39	1205
H40	12055
H41	1206
H42	1207
H43	1208
I2	099598100100
I3	099598100124
I4	099598105006
I5	099598105020
I6	099598105051
I7	099598105075
I8	099598108991
I9	099598108953
I10	099598108984
I11	099598108199
I12	099598108902
I13	099598109141
I14	099598111007
I15	099598111106
I16	099598111403
I17	099598111434
I18	099598111472
I19	099598111502
I20	099598111601
I21	023063104140
I22	099598312015
I23	023063105147
I24	023063755144
I25	023063105154
I26	023063102153
I27	099598112028
I28	023063102146
I29	099598312312
I30	099598312329
I31	099598312336
I32	023063751146
I33	099598312039
I34	023063101224
I35	099598412418
I36	099598412425
I37	099598412432
I38	099598412043
I39	023063100227
I40	023063700229
I41	023063101200
I42	023063501802
I43	023063601212
J2	1.25
J3	1.25
J4	0.6
J5	0.6
J6	0.6
J7	0.6
J8	45
J9	45
J10	45
J11	45
J12	45
J13	45
J14	1.5
J15	1.5
J16	1.5
J17	1.5
J18	1.5
J19	2.5
J20	2.5
J21	4.5
J22	18.5
J23	5.25
J24	5
J25	5.25
J26	5.25
J27	10.75
J28	4.5
J29	14
J30	15.75
J31	15.75
J32	6.75
J33	14
J34	6.25
J35	16.5
J36	18.25
J37	18.25
J38	16.5
J39	3.5
J40	3.75
J41	7.5
J42	6.75
J43	7.5
K2	0
K3	0
K4	0
K5	0
K6	0
K7	0
K8	0
K9	0
K10	0
K11	0
K12	0
K13	0
K14	0
K15	0
K16	0
K17	0
K18	0
K19	0
K20	0
K21	0
K22	0
K23	0
K24	0
K25	0
K26	0
K27	0
K28	0
K29	0
K30	0
K31	0
K32	0
K33	0
K34	0
K35	0
K36	0
K37	0
K38	0
K39	0
K40	0
K41	0
K42	0
K43	0
L2	10
L3	10
L4	10
L5	10
L6	10
L7	10
L8	10
L9	10
L10	10
L11	10
L12	10
L13	10
L14	10
L15	10
L16	10
L17	10
L18	10
L19	10
L20	10
L21	10
L22	10
L23	10
L24	10
L25	10
L26	10
L27	10
L28	10
L29	10
L30	10
L31	10
L32	10
L33	10
L34	10
L35	10
L36	10
L37	10
L38	10
L39	10
L40	10
L41	10
L42	10
L43	10
M2	1
M3	0
M4	1
M5	0
M6	0
M7	0
M8	1
M9	0
M10	0
M11	0
M12	0
M13	0
M14	1
M15	0
M16	0
M17	0
M18	0
M19	0
M20	0
M21	1
M22	1
M23	0
M24	0
M25	0
M26	0
M27	1
M28	0
M29	1
M30	1
M31	1
M32	0
M33	0
M34	0
M35	0
M36	0
M37	0
M38	0
M39	0
M40	0
M41	1
M42	0
M43	0
D8	4
D9	5
D10	8
D11	11
D12	14
D13	14
D29	3 OZ.
D30	3 OZ.
D31	3 OZ.
D33	3 OZ.
D35	4 OZ.
D36	4 OZ.
D37	4 OZ.
D38	4 OZ.

 

[gaz_chops]

Are they formatted as Hours:Minutes?

 

[gaz_chops]

Try this

=IF(RIGHT(B2,1)=1,1,0)

 

[SOLTEC]

No Gaz they're which image is primary and the 0's indicate a child that belongs to the primary.

 

[SOLTEC]

This just gave me all 0's all the way down. Did you notice that the column is not B, but F?

 

[Rick Rothstein]

Try this

=IF(RIGHT(B2,1)=1,1,0)

That will fail if there can be 11 (or 21, 31, etc) images under a default image. Also, your test was wrong... you tested a piece of text against the number 1 instead of a text 1. For your formula, look at the last 2 characters and check for ":1". You can simplify the resulting formula like this...

=0+(Right(F2,2)=":1")

Note I used F2, not B2 as that was the column those values were in.

 

[wynn89]

Hi,

You may want to try this

=IF(RIGHT(B2,LEN(B2)-FIND(":",B2))="1",1,0)

Regards,
Wynn

 

[SOLTEC]

Rick,

Thank you, that did the trick. I appreciate your input and insight.

 

[SOLTEC]

Wynn,

The column is not B it is F, but thank you also for your input and time.

 

[wynn89]

Wynn,

The column is not B it is F, but thank you also for your input and time.

Hi,

Change the B to F,

that should work

Regards,
Wynn