PDA

View Full Version : search by header column in VBA and copy corresponding column



BGerm
02-04-2020, 06:03 AM
Is it possible to search by header column in VBA?


Desired goal is when header is found, copy whole column to sheet2. Headers should be copied in order of their appearance in headers column. After 3 columns copied, blank column should be inserted, then next 3, then again blank column, and so on..


I managed to do it when headers are in list. But I am struggling when they are in column, not so experienced in VBA.




Headings

N37
D100556
10
338
N61
ND200011
7100339
100557
L000012
NLJD100340
JD1055


GUNJD100335

3
4
4
4
4
4
4
4
4
4
4


JD1055

4
5
5
5
5
5
5
5
5
5
5


LJD200008

5
10
7
10
7
10
7
10
7
10
7


N88LKG

7
9
8.33
9
8.33
9
8.33
9
8.33
9
8.33


N60

8.33
4
9.83
4
9.83
4
9.83
4
9.83
4
9.83


N0009

9.83
5
11.33
5
11.33
5
11.33
5
11.33
5
11.33


N37

11.33
10
12.83
10
12.83
10
12.83
10
12.83
10
12.83


D100556

12.83
9
14.33
9
14.33
9
14.33
9
14.33
9
14.33


B10

14.33
12
15.83
12
15.83
12
15.83
12
15.83
12
15.83


C338

15.83
14
17.33
14
17.33
14
17.33
14
17.33
14
17.33


N61

17.33
16
18.83
16
18.83
16
18.83
16
18.83
16
18.83


ND200011

18.83
18
20.33
18
20.33
18
20.33
18
20.33
18
20.33


K7100339

20.33
20
21.83
20
21.83
20
21.83
20
21.83
20
21.83


J100557

21.83
22
23.33
22
23.33
22
23.33
22
23.33
22
23.33


L000012

23.33
24
24.83
24
24.83
24
24.83
24
24.83
24
24.83


NLJD100340

24.83
1
1
1
1
1
1
1
1
1
1





2
2
2
2
2
2
2
2
2
2




















3
3
3
3
3
3
3
3
3
3

p45cal
02-05-2020, 07:04 AM
Please supply a sample workbook, preferably with a 'before' and 'after' sheet.

BGerm
02-05-2020, 07:45 AM
I manage to solve this one, but thank you anyways.
Now I have new issue :banghead: So I will change a post.

BGerm
02-05-2020, 07:53 AM
25924

I want to check if these combinatios are present anawhere in an excel sheet for each Father, Mother, Child. If they are highlight them, or bold, or in an empty column, in same row, write error.








Father
Mother
Child


0/0
0/0
1/1/


0/0
0/0
0/1


0/0
0/1
1/1/


0/0
1/1/
1/1/


0/0
1/1/
0/0


0/1
0/0
1/1/


0/1
1/1/
0/0


1/1/
0/0
0/0


1/1/
0/0
1/1/


1/1/
0/1
0/0


1/1/
1/1/
0/1


1/1/
1/1/
0/0


NaN
0/0
1/1/


NaN
1/1/
0/0


0/0
NaN
1/1/


1/1/
NaN
0/0

p45cal
02-05-2020, 12:10 PM
Work in progress: Does the attached highlight the correct rows?
There's conditional formatting in it.
(Ignore the formulae in cell G25 and below)

BGerm
02-06-2020, 01:43 AM
Actually my data frame is much bigger than one I sent here. So I think it is not handy to write it every trio if I got ur approach right.

p45cal
02-06-2020, 02:07 AM
Again, this is work in progress. Could you answer the question please?

BGerm
02-06-2020, 02:20 AM
When I open excel sheet you sent, nothing is highligted. Do I need to run something?

p45cal
02-06-2020, 03:27 AM
When I open excel sheet you sent, nothing is highligted.Oh, you're probably running Excel 2013.
If you edit the conditional formatting formula to:
=ISNUMBER(MATCH(G6 & "¬" & H6 & "¬" & I6,$A$25:$A$40 & "¬" & $B$25:$B$40 & "¬" & $C$25:$C$40,0)) it should highlight the first column of matching rows, but now I suspect this is wrong since it doesn't take into account headers in row 1 and the headers in row 24, but please confirm/deny.

BGerm
02-06-2020, 03:55 AM
yes, right, it is wrong.

I have done this:

It will write fail, in case where condition is true because these are unwanted combitions.


=WENN((UND(W6="0/0";X6="NaN";Y6="1/1/"));"Fail";WENN((UND(W6="1/1/";X6="NaN";Y6="0/0"));"Fail";WENN((UND(W6="0/0";X6="1/1/";Y6="NaN"));"Fail";WENN((UND(W6="1/1/";X6="0/0";Y6="NaN"));"Fail";WENN((UND(W6="0/0";X6="1/1/";Y6="1/1/"));"Fail";WENN((UND(W6="0/1";X6="1/1/";Y6="1/1/"));"Fail";WENN((UND(W6="0/0";X6="0/1";Y6="1/1/"));"Fail";WENN((UND(W6="1/1/";X6="0/0";Y6="1/1/"));"Fail";WENN((UND(W6="0/0";X6="0/0";Y6="1/1/"));"Fail";WENN((UND(W6="0/0";X6="1/1/";Y6="0/1"));"Fail";WENN((UND(W6="1/1/";X6="0/0";Y6="0/1"));"Fail";WENN((UND(W6="0/0";X6="1/1/";Y6="0/0"));"Fail";WENN((UND(W6="1/1/";X6="1/1/";Y6="0/0"));"Fail";WENN((UND(W6="1/1/";X6="0/1";Y6="0/0"));"Fail";WENN((UND(W6="0/1";X6="0/0";Y6="0/0"));"Fail";WENN((UND(W6="1/1/";X6="0/0";Y6="0/0"));"Fail"))))))))))))))))

Now I am trying to implement it in VBA. With loop, i,j,k, i child, j, mother, k, father. Step can be +4. But I do not know VBA syntax, so struggling with that.


Oh, you're probably running Excel 2013.
If you edit the conditional formatting formula to:
=ISNUMBER(MATCH(G6 & "¬" & H6 & "¬" & I6,$A$25:$A$40 & "¬" & $B$25:$B$40 & "¬" & $C$25:$C$40,0)) it should highlight the first column of matching rows, but now I suspect this is wrong since it doesn't take into account headers in row 1 and the headers in row 24, but please confirm/deny.

p45cal
02-06-2020, 04:58 AM
Is this picture right?:
25934

BGerm
02-06-2020, 05:10 AM
Yeees, it is :D

p45cal
02-06-2020, 05:47 AM
Conditional formatting in the attached.
Note a trailing space had to be removed from Child in cell C24.
Formula in E25 not needed but is the same as the conditional formatting formula.