[On going for learning] Looking for an excel expert advise...

ntk · 2016-04-24 02:02:13

What are the magic words to google for this excel function? Can you do brain storming? or asking your friends? and help me to find words so I can nail this excel function to do the following job:
The problem is:
I got the fields A1 to M1 reserved for the string "I am bad", which I have to chop down to character and fill in the reserved fields start from the right, with field M1 first, like in this example
A1=" "
B1=" "
C1=" "
D1=" "
E1=" "
F1="I"
G1=" "
H1="a"
I1="m"
J1=" "
K1="b"
L1="a"
M1="d"
How can I throw "I am bad" in excel, and let it perform the chopping, filling automatically ?

result like this "xxxxxIxamxbad" -where x are the empty spaces...

I know it can be done simply and quickly in perl, python or other scripting language, but problem is I need this solution for an excel sheet, because 90% of it already working autom, only this one step is missing to round it up

Last edited by ntk (2016-05-26 13:05:00)

0xFFFF · 2016-04-26 01:13:50

Nasty.

Not sure if this is exactly what you're after.
Assuming your text is in A1 and you're filling from A2 down...

=MID($A$1,ROW()-1,1)

ntk · 2016-04-26 05:08:34

Thanks 0xFFFF. I must have awaken the creator of the wiegand internet raw coding tool.

I don't fill it down This trick is for taking the FC or CN binary code and write them cell by cell in the 8 or 16bits reserved cells in the 26bit Wiegand sequence for further calculation.

maybe helpful to someone it is =MID($F$9,COLUMN(D9),1) when use horizontal

It did take me long time to figure out this and that too to complete this check tool because we learn to write from left to right. But we can say here is now a checking tool for Wiegand26bits of this form, IMO very clearly aimed for use on the current PM3 tools Wiegand computed
excel

It is ready for crunching any FC and CN accordingly to card limits, but I would like to complete the automation raw coding for Tecom 27bits, Wiegand 33, 34, 35, 37, 40bits and indala 26bis, AWID 26bits and Paxton 37bits also special AWID 50bits to 64bits RHB format too.

Also I am thinking about the revers tools, so basically it is similar like brivo card calculator guessing CN and FC based on a given HEX string... A Brivo but a cover of wider card area and data flow in both directions.

I heard in America HID entry access system is at every corner, so it is very intrigue to have a tool made by a student for a student... simple but supportive in case a student one day wonders what makes those doors at every corner tick ...

That tool would stick with Proxmark forum, and student can use it for checking their own effort to understand the raw coding of the available systems. ... I am not sure if it could work, but that dream is intention

Last edited by ntk (2016-05-26 13:10:21)

iceman · 2016-04-27 07:18:03

Good work ntk!

ntk · 2016-05-26 13:55:48

I need one more excel advise

I want to convert this number in excel, big hex number to bin, how can i do it?

for example

001070601D5559555569A999555656A6

or chopped down to if it is easier for you

00107060
1D555955
5569A999
555656A6

but I don't want to chop down manually more then that, do you know a way excel could/should do the job?

ntk · 2016-05-26 15:52:01

how to automatic changing value of certain cells in Excel

An other automate problem
I have three cells A1, B1 ,C1, I would like to change value of cell B1 to 0; value of cell C1 to 1, if value of cell 1 = an even number up to 5 digit long; otherwise if A1 is an odd, then B1 should be 1 and C1=0

how would you do it?

A1 in this case is sum of a calculation which effects on the cheksum bits, in this case 2 bits B1:C1. They takes either value '10' or '01' depends on the sum up result

Last edited by ntk (2016-05-26 15:57:03)

0xFFFF · 2016-05-26 23:43:59

ntk wrote:

I want to convert this number in excel, big hex number to bin, how can i do it?...

I would encourage you to use a programming language. C, C#, COBOL, Malbolge, anything... Please!

Assuming your data was in cell C1 you could use this

=(DEC2BIN(MOD(C1,256),8))

Because you're dealing with a large number you are using excel you would need to do this

=DEC2BIN(MOD(C1/16777216,256),8) & DEC2BIN(MOD(C1/65536,256),8) & DEC2BIN(MOD(C1/256,256),8) & (DEC2BIN(MOD(C1,256),8))

ntk wrote:

I have three cells A1, B1 ,C1, I would like to change value of cell B1 to 0; value of cell C1 to 1, if value of cell 1 = an even number up to 5 digit long; otherwise if A1 is an odd, then B1 should be 1 and C1=0

There is an ISEVEN function you could tie that in with MID, SUBSTITUTE, SUM. Should be easy enough.

ntk · 2016-05-27 01:44:03

thank you for your advise. 0xFFFF

I do use =(DEC2BIN(MOD(C1,256),8)) but above certain limit it gives you #number! error.

Also the combination for large number you propose i did already use some similar formula
=DEC2BIN(MOD(QUOTIENT(B18,256^3),256^2),8)&DEC2BIN(MOD(QUOTIENT(B18,256^2),256^1),8)&DEC2BIN(MOD(QUOTIENT(B18,256^1),256),8)&DEC2BIN(MOD(QUOTIENT(B18,256^0),256),8)
the problem is at limit of DEC ...

I can convert FFFFFFF to DEC but no bigger.

With your advise I will go back to think, and testing...

FFFFFFFF is 1111 1111 1111 1111 1111 1111 1111 1111 so easy I see it with closed eyes, I can do manually in a blink, why can a bl****dy computer, a over 30 yrs old excel the master of calculation not do it. A human brain still really amazes me.

As the second problem with the checksum if you could provide a small algorythm I would be ever in debt, when ever I fly over Autralia I will buy you a local beer. I have searched all sectors expert/professional/dummies the trick to do it, halo Excel exist over 30yrs and I could not find something ressemblant. Hence I have to turned to the expert on this forum, who daily in and out deals with bit bytes on a daily basis I hope so.

Currently I have to use conditional format method, I have a flag which depends on the value of the checksum covers the combination '10' or '01'!

I considered that very creative-any color I can dump on it-, highly emotional- it thankfully pulls me back from the moment I near hit my head against the keyboard, and quite elegantly -as to be honest I dun no what else to do-...

So why I ask you then for a new solution? That solution is impractical because sometimes you are so deep in sleepy mode that you see the flag, flags, flags ,flags ,, but you forgot it means "Go on to check or update the cs1 and cs2 value with 01 0r 10" and your beautiful program create some embarrassing result, even it has computed 99.9999% the calculation correctly

So, to conserve the right at any time one can act sleepy, I need that fill automation trick, if you manage to carve it out, and confident enough seeing me to check it for you, pls tell me, but please dont ask me learning any programming language to do that... I reserve the right to act sleepy, not to computing crazy just for replacing that little, meaningfulless pity flag covering trick.

Last edited by ntk (2016-05-27 01:45:30)

ntk · 2016-05-27 02:42:01

the aim is to convert from 32 bytes down to 8bytes automatically.

my example is to automat convert the HEX 7cca7b605,

I misreported a fault with limit previuosly, I have tested hex2dec(ffffffff) is still possible =4294967295

So we can use that to convert to bin. Here your formula is better then mine both can do up to fffffffff, convert it to 1111 1111 1111 1111 1111 1111 1111 1111

but if seeing 0fffffffff, mine will fail; your still do, although it does not see the 0. te calculation could go on and that can be dangerous.

So it seems to have no other way then use that way
your ways
=DEC2BIN(MOD(b6/16777216,256),8) & DEC2BIN(MOD(b6/65536,256),8) & DEC2BIN(MOD(b6/256,256),8) & (DEC2BIN(MOD(b6,256),8))
=DEC2BIN(MOD(b6/256^3),8) & DEC2BIN(MOD(b6/256^2,256),8) & DEC2BIN(MOD(b6/256^1,256),8) & (DEC2BIN(MOD(b6/256^0,256),8))

and if I want to auto convert upto 2 data blocks or 16 bytes it got to be
DEC2BIN(MOD(B6/256^7,256),8)&DEC2BIN(MOD(B6/256^6,256),8)&DEC2BIN(MOD(B6/256^5,256),8)&(DEC2BIN(MOD(B6/256^4,256),8))&DEC2BIN(MOD(B6/256^3,256),8)&DEC2BIN(MOD(B6/256^2,256),8)&DEC2BIN(MOD(B6/256^1,256),8)&(DEC2BIN(MOD(B6/256^0,256),8))

Ummmmm, horrible looking but beautiful with all the paddings, thank you for sharing your idea.

But... I test and see it still failed,
can not solve more than 9bytes

there was where I saw HEX2DEC hit a llimit

Last edited by ntk (2016-05-27 03:12:52)

0xFFFF · 2016-05-27 04:14:57

ntk wrote:

thank you for your advise. 0xFFFF

You're welcome

ntk wrote:

...but please dont ask me learning any programming language to do that...

No probs.

I'm not sure I understand your problem so this answer is mostly a guess...

Check the count of 1 bits between pos 2 and 12 of cell C1 is even

ISEVEN(LEN(SUBSTITUTE(MID(C1,2,12),"1","")))

ntk · 2016-05-27 05:13:04

Thank you thank you thank you, seeing you make effort trying to answer my problem, I have had courage again to sit on that chksum bit updating problem tonight, Well I was proud of the ingenious flag covering idea, but I was not happy with that... I knew one day I will make mistake forgetting to check the chksum "flag"

And thank to your effort, I just fumble, fumble and I just succeed to manage it, it is amaze me sometimes when the brain does click, as for days, weeks ago I gave up the hope already...

Here is my previous "ingenious" solution flag covers what you should not see, so not use value for CS bit Do you see the red big number, the bits CS1, CS2 in BK18:BK18 and mine "famous" flag covers over field AE22?

and here is the result of my experiment today. so that Excel indala tool table is really fully automatic from tonight .... No more living under fear with 99,9999% correctness.

in C2 I have the formula "=SUM(A2:B2)" in E2 I have "=IF(ISEVEN(C2),1,0)" in F2 "=IF(ISEVEN(C2),0,1)" could understand why my did not clcik for so long

Thanks to you encouraging, 0xFFFF. It is good that one has a forum to ask his/her question...

0xFFFF · 2016-05-27 05:58:46

Thanks for the feedback ntk
Glad to hear you solved it.

Proxmark3 community

Announcement

#1 2016-04-24 02:02:13

[On going for learning] Looking for an excel expert advise...

#2 2016-04-26 01:13:50

Re: [On going for learning] Looking for an excel expert advise...

#3 2016-04-26 05:08:34

Re: [On going for learning] Looking for an excel expert advise...

#4 2016-04-27 07:18:03

Re: [On going for learning] Looking for an excel expert advise...

#5 2016-05-26 13:55:48

Re: [On going for learning] Looking for an excel expert advise...

#6 2016-05-26 15:52:01

Re: [On going for learning] Looking for an excel expert advise...

#7 2016-05-26 23:43:59

Re: [On going for learning] Looking for an excel expert advise...

#8 2016-05-27 01:44:03

Re: [On going for learning] Looking for an excel expert advise...

#9 2016-05-27 02:42:01

Re: [On going for learning] Looking for an excel expert advise...

#10 2016-05-27 04:14:57

Re: [On going for learning] Looking for an excel expert advise...

#11 2016-05-27 05:13:04

Re: [On going for learning] Looking for an excel expert advise...

#12 2016-05-27 05:58:46

Re: [On going for learning] Looking for an excel expert advise...

Board footer