Converting data

[jo83]

Hello.

I hope that you are able to help me with my problem. I have have the following data:

	A	B	C	D
1	Date: 12/01/20123
2	Poscode: 2200
3
4	Unique ID	Risk	Relevance
5	AKFD	H	Mapping	5
6	AFEJ	L	Connection	6
7	FHFB	H	Connection	7
8
9	Date: 14/01/20123
10	Poscode: 2200
11
12	Unique ID	Risk	Relevance
13	DHWJ	H	Connection	13
14	DJEU	L	Connection	14
15	DHEY	L	Mapping	15

<tbody>
</tbody>

The D column is based on the following formula: =IF(LEFT(C1;2)=LEFT($D$11;2);ROW(O1);"")

I would like Excel to scan the sheet and create the following table:

	E	F	G
1	Unique ID	Risk	Relevance
2	AKFD	H	Mapping
3	AFEJ	L	Connection
4	FHFB	H	Connection
5	DHWJ	H	Connection
6	DJEU	L	Connection
7	DHEY	L	Mapping

<tbody>
</tbody>

What type of formula should i use in cell E2 so it would jump directly to the second table and disptey the needed data?

Thank you very much!!

 

[Akashwani]

Hi and welcome to MrExcel.

The following is based on the assumption that the UniqueID is the ONLY data in column A that is 4 characters in length.
IF there are ANY other cells that contain data with 4 characters, then this solution will fail and I DON'T have a solution for that, sorry.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date: 12/01/2012	*	*	6	Unique ID	Risk	Relevance	*
2	Poscode: 2200	*	*	*	AKFD	H	Mapping	*
3	*	*	*	*	AFEJ	L	Connection	*
4	Unique ID	Risk	Relevance	*	FHFB	H	Connection	*
5	AKFD	H	Mapping	5	DHWJ	H	Connection	*
6	AFEJ	L	Connection	6	DJEU	L	Connection	*
7	FHFB	H	Connection	7	DHEY	L	Mapping	*
8	*	*	*	*	*	*	*	*
9	Date: 14/01/2012	*	*	*	*	*	*	*
10	Poscode: 2200	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*
12	Unique ID	Risk	Relevance	*	*	*	*	*
13	DHWJ	H	Connection	13	*	*	*	*
14	DJEU	L	Connection	14	*	*	*	*
15	DHEY	L	Mapping	15	*	*	*	*
16	*	*	*	*	*	*	*	*
Sheet14

The formula in E2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
The formula in F2 needs to be copied across and down.

ALL formulas will need to have the cell ranges changed to suit your layout.

I hope that helps.

Good luck.

Ak

 

[jo83]

Thank you so much for the quick response .
The problem is that it is not. But does it help that the Unique ID always start with exactly the same 4 characters? For example:
JATJ-563.01-R
JATJ-652.02-R
JATJ-646.02-R

 

[Akashwani]

Hi,

It's always best to give the correct information when asking a question!!

Does this work for you?......

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date: 12/01/2012		JATJ	6	Unique ID	Risk	Relevance
2	Poscode: 2200				JATJ-563.01-R	H	Mapping
3					JATJ-652.02-R	L	Connection
4	Unique ID	Risk	Relevance		JATJ-646.02-R	H	Connection
5	JATJ-563.01-R	H	Mapping	5	JATJ-563.01-T	H	Connection
6	JATJ-652.02-R	L	Connection	6	JATJ-652.02-T	L	Connection
7	JATJ-646.02-R	H	Connection	7	JATJ-646.02-T	L	Mapping
8
9	Date: 14/01/2012
10	Poscode: 2200
11
12	Unique ID	Risk	Relevance
13	JATJ-563.01-T	H	Connection	13
14	JATJ-652.02-T	L	Connection	14
15	JATJ-646.02-T	L	Mapping	15
16
Sheet14

The formula in E2 needs entering with ctrl shift enter NOT just enter, it can then be copied down.
The formula in F2 needs to be copied across and down.

ALL formulas will need to have the cell ranges changed to suit your layout.

I hope that helps.

Good luck.

Ak