Common Value

[Waqas ali]

Dear All,

i have a table named actual. i want to get in query as result.

from actual if Jobid last 4 digits are same than first value should come with second value.

example in result table is shown. i prepared it manually but i want to get through query.

how to write an query?

Actual		Result
Name	Jobid	Location	site	JobType		Name	Jobid	Comon	Location	site	JobType
John	178004	17-DHW	west	D		John	178004	178004	17-DHW	west	D
Dedy	198004	18-FEQ	east	D		Dedy	198004	178004	18-FEQ	east	D
Pop	259940	11-LOB	west	D		Pop	259940	259940	11-LOB	west	D
Jacob	279940	15-LOB	east	D		Jacob	279940	259940	15-LOB	east	D
Udhay	314563	21-TOS	west	L		Udhay	314563	314563	21-TOS	west	L
Ashish	334563	23-TOS	east	L		Ashish	334563	314563	23-TOS	east	L

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

What if we have a Waqas entry in Actual with Jobid = 408004? That means three entries where the last four digits are the same.

 

[Waqas ali]

yes it can be but how to put first one which is lower value to put in next column with matching with last four digits.

 

[Aladin Akyurek]

yes it can be but how to put first one which is lower value to put in next column with matching with last four digits.

Sorry, I'm not following...

How about...

Row\Col	A​	B​	C​	D​	E​	F​
2​	Name	Jobid	Location	site	JobType	common
3​	John	178004​	17-DHW	west	D	178004
4​	Dedy	198004​	18-FEQ	east	D	178004 | 408004
5​	Pop	259940​	11-LOB	west	D	259940
6​	Jacob	279940​	15-LOB	east	D	259940
7​	Udhay	314563​	21-TOS	west	L	314563
8​	Ashish	334563​	23-TOS	east	L	314563
9​	Waqas	408004​	10-XAD	north	U	178004 | 198004

which does the processing in an additional column?

 

[Waqas ali]

dear Aladin,

i need in this way.

ow\Col	A​	B​	C​	D​	E​	F​
2​	Name	Jobid	Location	site	JobType	common
3​	John	178004​	17-DHW	west	D	178004
4​	Dedy	198004​	18-FEQ	east	D	178004
5​	Pop	259940​	11-LOB	west	D	259940
6​	Jacob	279940​	15-LOB	east	D	259940
7​	Udhay	314563​	21-TOS	west	L	314563
8​	Ashish	334563​	23-TOS	east	L	314563
​

<tbody>
</tbody>

 

[Aladin Akyurek]

dear Aladin,

i need in this way.

ow\Col	A​	B​	C​	D​	E​	F​
2​	Name	Jobid	Location	site	JobType	common
3​	John	178004​	17-DHW	west	D	178004
4​	Dedy	198004​	18-FEQ	east	D	178004
5​	Pop	259940​	11-LOB	west	D	259940
6​	Jacob	279940​	15-LOB	east	D	259940
7​	Udhay	314563​	21-TOS	west	L	314563
8​	Ashish	334563​	23-TOS	east	L	314563
​

<tbody>
</tbody>

Dear Waqas ali,

What did you do with the Waqas entry?

 

[Waqas ali]

dear aladin,

jobid first two digits always odd number it will not in even number in my reports.

 

[Aladin Akyurek]

dear aladin,

jobid first two digits always odd number it will not in even number in my reports.

Do you mean that there will never be more than two entries (job id's) whose last four digits are equal?

 

[Waqas ali]

yes

 

[Aladin Akyurek]

Do you mean that there will never be more than two entries (job id's) whose last four digits are equal?

yes

Row\Col	A​	B​	C​	D​	E​	F​
1​	Name	Jobid	Location	site	JobType	Common
2​	John	178004​	17-DHW	west	D	178004​
3​	Dedy	198004​	18-FEQ	east	D	178004​
4​	Pop	259940​	11-LOB	west	D	259940​
5​	Jacob	279940​	15-LOB	east	D	259940​
6​	Udhay	314563​	21-TOS	west	L	314563​
7​	Ashish	334563​	23-TOS	east	L	314563​

F2, copied down:

=LOOKUP(9.99999999999999E+307,
   CHOOSE({1,2},B2,LOOKUP(9.99999999999999E+307,
    FIND(RIGHT($B2,4),$B$1:B1),$B$1:B1)))