How to create a Boolean strings in excel with VBA Macro

satish78 · Jan 31, 2019

Hi Friends,

I tried with a formula creating Boolean strings but its time consuming every time.
This is an example:
In Column, AI have list of words from A1:A50, It varies every time from A1:A50.
ColumnB AND words
ColumnC NOT words

Here I want to build Boolean strings in ColumnD.

In ColumnD, I need formula like this in image shown below while taking all keywords from A1:A50(sometimes A1:A9 or A1:A11 or A1;A13 or A1:A29 and so on) with "AND" and "NOT" words from respective columns(ColumnB and ColumnC)
Here I need Macro to display a popup box to input range of keywords(from ColumnA) to be present by default in each query/Boolean shown in image.

Suggestions would be highly appreciated.

https://spaces.hightail.com/space/Jfldl

EFANYoutube · Jan 31, 2019

Sorry satish
I find your post confusing
please explain your project as a whole a bit more so we can connect the dots

satish78 · Jan 31, 2019

Did you see that image link. Its an example of what I am looking for.
In the image we have only of 6 rows shown. So, I need more rows (a1:a50) in a Macro enabled excel sheet.

Please find an example of excel file with details.

https://spaces.hightail.com/space/PCFdL4MDHJ

shg · Jan 31, 2019

You want a result like this?

1	A	AND B	AND C	AND D	AND E	AND F
2	A	AND B	AND C	AND D	AND E	AND NOT F
3	A	AND B	AND C	AND D	AND NOT E	AND F
4	A	AND B	AND C	AND D	AND NOT E	AND NOT F
5	A	AND B	AND C	AND NOT D	AND E	AND F
6	A	AND B	AND C	AND NOT D	AND E	AND NOT F
7	A	AND B	AND C	AND NOT D	AND NOT E	AND F
8	A	AND B	AND C	AND NOT D	AND NOT E	AND NOT F
9	A	AND B	AND NOT C	AND D	AND E	AND F
10	A	AND B	AND NOT C	AND D	AND E	AND NOT F
11	A	AND B	AND NOT C	AND D	AND NOT E	AND F
12	A	AND B	AND NOT C	AND D	AND NOT E	AND NOT F
13	A	AND B	AND NOT C	AND NOT D	AND E	AND F
14	A	AND B	AND NOT C	AND NOT D	AND E	AND NOT F
15	A	AND B	AND NOT C	AND NOT D	AND NOT E	AND F
16	A	AND B	AND NOT C	AND NOT D	AND NOT E	AND NOT F
17	A	AND NOT B	AND C	AND D	AND E	AND F
18	A	AND NOT B	AND C	AND D	AND E	AND NOT F
19	A	AND NOT B	AND C	AND D	AND NOT E	AND F
20	A	AND NOT B	AND C	AND D	AND NOT E	AND NOT F
21	A	AND NOT B	AND C	AND NOT D	AND E	AND F
22	A	AND NOT B	AND C	AND NOT D	AND E	AND NOT F
23	A	AND NOT B	AND C	AND NOT D	AND NOT E	AND F
24	A	AND NOT B	AND C	AND NOT D	AND NOT E	AND NOT F
25	A	AND NOT B	AND NOT C	AND D	AND E	AND F
26	A	AND NOT B	AND NOT C	AND D	AND E	AND NOT F
27	A	AND NOT B	AND NOT C	AND D	AND NOT E	AND F
28	A	AND NOT B	AND NOT C	AND D	AND NOT E	AND NOT F
29	A	AND NOT B	AND NOT C	AND NOT D	AND E	AND F
30	A	AND NOT B	AND NOT C	AND NOT D	AND E	AND NOT F
31	A	AND NOT B	AND NOT C	AND NOT D	AND NOT E	AND F
32	A	AND NOT B	AND NOT C	AND NOT D	AND NOT E	AND NOT F
33	NOT A	AND B	AND C	AND D	AND E	AND F
34	NOT A	AND B	AND C	AND D	AND E	AND NOT F
35	NOT A	AND B	AND C	AND D	AND NOT E	AND F
36	NOT A	AND B	AND C	AND D	AND NOT E	AND NOT F
37	NOT A	AND B	AND C	AND NOT D	AND E	AND F
38	NOT A	AND B	AND C	AND NOT D	AND E	AND NOT F
39	NOT A	AND B	AND C	AND NOT D	AND NOT E	AND F
40	NOT A	AND B	AND C	AND NOT D	AND NOT E	AND NOT F
41	NOT A	AND B	AND NOT C	AND D	AND E	AND F
42	NOT A	AND B	AND NOT C	AND D	AND E	AND NOT F
43	NOT A	AND B	AND NOT C	AND D	AND NOT E	AND F
44	NOT A	AND B	AND NOT C	AND D	AND NOT E	AND NOT F
45	NOT A	AND B	AND NOT C	AND NOT D	AND E	AND F
46	NOT A	AND B	AND NOT C	AND NOT D	AND E	AND NOT F
47	NOT A	AND B	AND NOT C	AND NOT D	AND NOT E	AND F
48	NOT A	AND B	AND NOT C	AND NOT D	AND NOT E	AND NOT F
49	NOT A	AND NOT B	AND C	AND D	AND E	AND F
50	NOT A	AND NOT B	AND C	AND D	AND E	AND NOT F
51	NOT A	AND NOT B	AND C	AND D	AND NOT E	AND F
52	NOT A	AND NOT B	AND C	AND D	AND NOT E	AND NOT F
53	NOT A	AND NOT B	AND C	AND NOT D	AND E	AND F
54	NOT A	AND NOT B	AND C	AND NOT D	AND E	AND NOT F
55	NOT A	AND NOT B	AND C	AND NOT D	AND NOT E	AND F
56	NOT A	AND NOT B	AND C	AND NOT D	AND NOT E	AND NOT F
57	NOT A	AND NOT B	AND NOT C	AND D	AND E	AND F
58	NOT A	AND NOT B	AND NOT C	AND D	AND E	AND NOT F
59	NOT A	AND NOT B	AND NOT C	AND D	AND NOT E	AND F
60	NOT A	AND NOT B	AND NOT C	AND D	AND NOT E	AND NOT F
61	NOT A	AND NOT B	AND NOT C	AND NOT D	AND E	AND F
62	NOT A	AND NOT B	AND NOT C	AND NOT D	AND E	AND NOT F
63	NOT A	AND NOT B	AND NOT C	AND NOT D	AND NOT E	AND F
64	NOT A	AND NOT B	AND NOT C	AND NOT D	AND NOT E	AND NOT F

satish78 · Jan 31, 2019

I want A, B, C be default in each query no change except other rows.
Till row8 is correct I do not need after 8th row which does not make sense to me. Because, we must and should have A, B, C data by default in each query.
D, E, F rows getting interchanged correctly till row8.

shg · Jan 31, 2019

Sorry, I don't follow that.

shg · Jan 31, 2019

Ah - I get it:




A	AND B	AND C	AND D	AND E	AND F
			AND NOT D	AND NOT E	AND NOT F

1	A	AND B	AND C	AND D	AND E	AND F
2	A	AND B	AND C	AND D	AND E	AND NOT F
3	A	AND B	AND C	AND D	AND NOT E	AND F
4	A	AND B	AND C	AND D	AND NOT E	AND NOT F
5	A	AND B	AND C	AND NOT D	AND E	AND F
6	A	AND B	AND C	AND NOT D	AND E	AND NOT F
7	A	AND B	AND C	AND NOT D	AND NOT E	AND F
8	A	AND B	AND C	AND NOT D	AND NOT E	AND NOT F

[td="bgcolor:#F3F3F3"]

Cumu:

[/td][td="bgcolor:#F0F0F0"]

8

[/td][td="bgcolor:#F0F0F0"]

8

[/td][td="bgcolor:#F0F0F0"]

8

[/td][td="bgcolor:#F0F0F0"]

8

[/td][td="bgcolor:#F0F0F0"]

4

[/td][td="bgcolor:#F0F0F0"]

2

[/td] [td="bgcolor:#F3F3F3"]

Cnt:

[/td][td="bgcolor:#F0F0F0"]

1

[/td][td="bgcolor:#F0F0F0"]

1

[/td][td="bgcolor:#F0F0F0"]

1

[/td][td="bgcolor:#F0F0F0"]

2

[/td][td="bgcolor:#F0F0F0"]

2

[/td][td="bgcolor:#F0F0F0"]

2

[/td] [td="bgcolor:#F3F3F3"]

Header:

[/td] [td="bgcolor:#F3F3F3"]

Inputs:

[/td] [td="bgcolor:#909090"][/td] [td="bgcolor:#F3F3F3"]

#

[/td][td="bgcolor:#F3F3F3"]

[/td]

Workbook at https://app.box.com/s/47b28f19d794b25511be

satish78 · Jan 31, 2019

shg said:
You want a result like this?

1
A
AND B
AND C
AND D
AND E
AND F
2
A
AND B
AND C
AND D
AND E
AND NOT F
3
A
AND B
AND C
AND D
AND NOT E
AND F
4
A
AND B
AND C
AND D
AND NOT E
AND NOT F
5
A
AND B
AND C
AND NOT D
AND E
AND F
6
A
AND B
AND C
AND NOT D
AND E
AND NOT F
7
A
AND B
AND C
AND NOT D
AND NOT E
AND F
8
A
AND B
AND C
AND NOT D
AND NOT E
AND NOT F
9
A
AND B
AND NOT C
AND D
AND E
AND F
10
A
AND B
AND NOT C
AND D
AND E
AND NOT F
11
A
AND B
AND NOT C
AND D
AND NOT E
AND F
12
A
AND B
AND NOT C
AND D
AND NOT E
AND NOT F
13
A
AND B
AND NOT C
AND NOT D
AND E
AND F
14
A
AND B
AND NOT C
AND NOT D
AND E
AND NOT F
15
A
AND B
AND NOT C
AND NOT D
AND NOT E
AND F
16
A
AND B
AND NOT C
AND NOT D
AND NOT E
AND NOT F
17
A
AND NOT B
AND C
AND D
AND E
AND F
18
A
AND NOT B
AND C
AND D
AND E
AND NOT F
19
A
AND NOT B
AND C
AND D
AND NOT E
AND F
20
A
AND NOT B
AND C
AND D
AND NOT E
AND NOT F
21
A
AND NOT B
AND C
AND NOT D
AND E
AND F
22
A
AND NOT B
AND C
AND NOT D
AND E
AND NOT F
23
A
AND NOT B
AND C
AND NOT D
AND NOT E
AND F
24
A
AND NOT B
AND C
AND NOT D
AND NOT E
AND NOT F
25
A
AND NOT B
AND NOT C
AND D
AND E
AND F
26
A
AND NOT B
AND NOT C
AND D
AND E
AND NOT F
27
A
AND NOT B
AND NOT C
AND D
AND NOT E
AND F
28
A
AND NOT B
AND NOT C
AND D
AND NOT E
AND NOT F
29
A
AND NOT B
AND NOT C
AND NOT D
AND E
AND F
30
A
AND NOT B
AND NOT C
AND NOT D
AND E
AND NOT F
31
A
AND NOT B
AND NOT C
AND NOT D
AND NOT E
AND F
32
A
AND NOT B
AND NOT C
AND NOT D
AND NOT E
AND NOT F
33
NOT A
AND B
AND C
AND D
AND E
AND F
34
NOT A
AND B
AND C
AND D
AND E
AND NOT F
35
NOT A
AND B
AND C
AND D
AND NOT E
AND F
36
NOT A
AND B
AND C
AND D
AND NOT E
AND NOT F
37
NOT A
AND B
AND C
AND NOT D
AND E
AND F
38
NOT A
AND B
AND C
AND NOT D
AND E
AND NOT F
39
NOT A
AND B
AND C
AND NOT D
AND NOT E
AND F
40
NOT A
AND B
AND C
AND NOT D
AND NOT E
AND NOT F
41
NOT A
AND B
AND NOT C
AND D
AND E
AND F
42
NOT A
AND B
AND NOT C
AND D
AND E
AND NOT F
43
NOT A
AND B
AND NOT C
AND D
AND NOT E
AND F
44
NOT A
AND B
AND NOT C
AND D
AND NOT E
AND NOT F
45
NOT A
AND B
AND NOT C
AND NOT D
AND E
AND F
46
NOT A
AND B
AND NOT C
AND NOT D
AND E
AND NOT F
47
NOT A
AND B
AND NOT C
AND NOT D
AND NOT E
AND F
48
NOT A
AND B
AND NOT C
AND NOT D
AND NOT E
AND NOT F
49
NOT A
AND NOT B
AND C
AND D
AND E
AND F
50
NOT A
AND NOT B
AND C
AND D
AND E
AND NOT F
51
NOT A
AND NOT B
AND C
AND D
AND NOT E
AND F
52
NOT A
AND NOT B
AND C
AND D
AND NOT E
AND NOT F
53
NOT A
AND NOT B
AND C
AND NOT D
AND E
AND F
54
NOT A
AND NOT B
AND C
AND NOT D
AND E
AND NOT F
55
NOT A
AND NOT B
AND C
AND NOT D
AND NOT E
AND F
56
NOT A
AND NOT B
AND C
AND NOT D
AND NOT E
AND NOT F
57
NOT A
AND NOT B
AND NOT C
AND D
AND E
AND F
58
NOT A
AND NOT B
AND NOT C
AND D
AND E
AND NOT F
59
NOT A
AND NOT B
AND NOT C
AND D
AND NOT E
AND F
60
NOT A
AND NOT B
AND NOT C
AND D
AND NOT E
AND NOT F
61
NOT A
AND NOT B
AND NOT C
AND NOT D
AND E
AND F
62
NOT A
AND NOT B
AND NOT C
AND NOT D
AND E
AND NOT F
63
NOT A
AND NOT B
AND NOT C
AND NOT D
AND NOT E
AND F
64
NOT A
AND NOT B
AND NOT C
AND NOT D
AND NOT E
AND NOT F

<tbody>
</tbody>

Can you send above Macro file? Let me check.

shg · Jan 31, 2019

Link in post 7.

satish78 · Jan 31, 2019

its difficult to understand.
It is not exactly as I shown in my excel file format.
Each query/Boolean is into different columns not in single cell.

How to create a Boolean strings in excel with VBA Macro

satish78

Board Regular

Excel Facts

EFANYoutube

Active Member

satish78

Board Regular

shg

MrExcel MVP

satish78

Board Regular

shg

MrExcel MVP

shg

MrExcel MVP

satish78

Board Regular

shg

MrExcel MVP

satish78

Board Regular

Similar threads

Forum statistics

Share this page

How to create a Boolean strings in excel with VBA Macro

Board Regular

Excel Facts

Active Member

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock