Use a cell with specific text as reference to create a table from data in other cells

[Scorsese]

Hello all, first post over here, hope I can get to explain myself,

Im exporting data from word to excel and I got info in a mostly an arranged layout.Now Im trying to get a table with the info I need from that data arranged, and I am sure that I can use a cell "B2,B7"(PCS) with specific text as a reference to get to the data I want I just dont know what formula to use so I can use that cell as a reference and star Picking up the data from each set in a table.

Excel 2012

	A	B	C	D	E	F	G
3			MX	ROTOR	0
4	50	PCS		$5.62	MX	8414.90.3000 0.00	$281.11
5	163	KG NT
6	163	KG GR
7	22		NERS
8			MX	ROTOR
9	3	PCS		$5.79	MX	8414.90.3000 0.00	$17.37
10	11	KG NT
11	11	KG GR
12	2	SKIDS
13			MX	STATOR	CU
14	16	PCS		$174.64	MX	8414.90.3000 0.00	$2,794.28
15	816	KG NT
16	816	KG GR
17	1	BOX
18			MX	STATOR	CU
19	24	PCS		$187.82	MX	8414.90.3000 0.00	$4,507.67
20	1,043.00	KG NT
21	1,043.00	KG GR
22			MX	'ROTOR	50
23	160	PCS		$6.93	MX	8414.90.3000 0.00	$1,108.01
24	566	KG NT
25	566	KG GR

<tbody>
</tbody>

testrule30.2​

And what Im trying to get is the following:

Excel 2012

	A	B	C	D	E	F	G
2	ROTOR	50	163	163	281.11	MX	8414.90.3000 0.00
3	ROTOR	3	11	11	17.37	MX	8414.90.3000 0.00
4	STATOR	16	816	816	2794.28	MX	8414.90.3000 0.00
5	STATOR	24	1043	1043	4507.67	MX	8414.90.3000 0.00
6	'ROTOR	160	566	566	1108.01	MX	8414.90.3000 0.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Im really lost, I dont know what formulas I could use to do that. any help is really appreciated






​

 

[WarfritLive]

We can use formulas for this, but the data looks pretty uniform to me. My first thought is to copy paste each column over individually after using the filter button to remove blanks / separate whatever data you need. (ie select only 'KG NT' and copy the amounts in column A.)

If you only have to do this once, it'll probably be faster

 

[Scorsese]

We can use formulas for this, but the data looks pretty uniform to me. My first thought is to copy paste each column over individually after using the filter button to remove blanks / separate whatever data you need. (ie select only 'KG NT' and copy the amounts in column A.)

If you only have to do this once, it'll probably be faster

Thanks for your quick reply WarfritLive, but actually I am trying to replicate this with similar data multiple times in the future, right now I want to write down the formulas that would arrange the data and then a macro with them.

 

[WarfritLive]

Okay, here's what I have. I hate helper columns but I've spent too much time trying to work around them.
This should work for you.

Formulas:
A3: =IF(F3="MX",A2+1,A2)
A4: =IF(F4="MX",A3+1,A3)
a5: =IF(F5="MX",A4+1,A4)
etc

B3: =IF(A3=A2,B2+1,1)
B4: =IF(A4=A3,B3+1,1)
B5: =IF(A5=A4,B4+1,1)
etc

C3: =CONCATENATE(A3,".",B3)
C4: =CONCATENATE(A4,".",B4)
C5: =CONCATENATE(A5,".",B5)
etc

E29: =VLOOKUP(ROW(A1),$A$3:$J$25,7,FALSE)
F29:=SUMPRODUCT(--(A3:A25'=ROW(A1)),--(B3:B25'=2),(D3:D25))
G29: =SUMPRODUCT(--(A3:A25'=ROW(A1)),--(B3:B25'=3),(D3:D25))
H29: =SUMPRODUCT(--(A3:A25'=ROW(A1)),--(B3:B25'=4),(D3:D25))
I29: =VLOOKUP(CONCATENATE(ROW(A1),".2"),$C$3:$J$25,8,FALSE)
J29: =VLOOKUP(CONCATENATE(ROW(A1),".2"),$C$3:$J$25,6,FALSE)
K29: =VLOOKUP(CONCATENATE(ROW(A1),".2"),$C$3:$J$25,7,FALSE)
Autofill down as needed