copyboy007
Board Regular
- Joined
- May 17, 2005
- Messages
- 60
Am trying to extract data from an exported paste of raw data in Column B (column A was only added for reference) of invoices (some include payments to subcontractors plus a subtotal), which merged and split some items (e.g., invoice numbers split into two rows; amounts were merged with text and dates). The formulas I've researched are in a grey shade when I was brainstorming how to group the invoice items and extract whatever I can. It also exported the page headers between page breaks, which is throwing off some formula results. The last two columns on the right that are not shaded, which contain sample data of what I'm trying to extract for each line. But, this still needs tweaks (or overhauling). Can this generic export become a proper data source to generate a pivot table that sees how every item is connected to every invoice.
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2:C49 | C2 | =IF(AND(D2=0,D3<>0),A2,0) |
D2:D49 | D2 | =IF(AND(E2=0,E3<>0),A2,0) |
E2:E49 | E2 | =IF(OR(ISNUMBER(SEARCH({") $","total subconsultant cost"},B2))),A2,0) |
F2:F49 | F2 | =IF(AND(E2=0,E1<>0),A2,0) |
G2:G49 | G2 | =IFERROR(LOOKUP(2,1/(C2:F2>0),C2:F2),"") |
H2:H49 | H2 | =IF(OR(K2="xxx",C2<>0),MID($B2,FIND(" ",$B2)+1,99)&$B3,H1) |
I2:I49 | I2 | =IF(C2<>0,MID($B2,FIND(" ",$B2)+1,99)&$B3,IF(G2="","",I1)) |
J2:J49 | J2 | =IF(K2="xxx",MID($B2,FIND(" ",$B2)+1,99)&$B3,IF(G2="",J1,"")) |
K2:K49 | K2 | =IF(COUNTIF(B1,"*/*/*"),"xxx","") |
L2:L49 | L2 | =IF(LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))>1,"Date","") |
M2:M49 | M2 | =IF(ISNUMBER(SEARCH(") $",B2)),MID($B2,FIND(" $",$B2)+1,99)+0,"") |
N2:N49 | N2 | =IF(ISNUMBER(SEARCH(") $",B2)),LEFT(B2,(FIND(") $",B2)-0)),"") |