smide
Board Regular
- Joined
- Dec 20, 2015
- Messages
- 162
- Office Version
- 2016
- Platform
- Windows
Hello.
I need some assistance with an excel spreadsheet which contains very raw output report in row 1 columns (A-ZW).
I'm not very proficient with excel-vba but I'm open to suggestions and willing to learn to get this to work properly.
As I already stated in row1 in Sheet2 I have a raw report output which contains certain Product prices.
Each product is presented with three prices: shop, factory and market.
The problem is that I need to find cells that contain this data about product's prices, then to extract only numbers (prices) from those cells without unnecessary characters in those cells.
The cells which contain product's characteristics (all cells are in row 1,Sheet2 as I said) always follow the next pattern:
Example.
Sheet2
cell (1,D) product:{"shop":15.80
cell (1,E) factory:10.40
cell (1,F) market:14.20}
(cells with this characteristics/special characters/brackets are always consecutive cells in row 1 (Sheet2))
I need this price data formated as some sort of data frame/table starting from cell B2 in Sheet1 in following format:
Sheet1
cell (2,B) 15.80
Cell (2,C) 10.40
cell (2,D) 14.20
Extended example
Sheet2 (raw report)
<tbody>
</tbody>
Sheet1 (after macro or formula run)
<tbody>
</tbody>
I need some assistance with an excel spreadsheet which contains very raw output report in row 1 columns (A-ZW).
I'm not very proficient with excel-vba but I'm open to suggestions and willing to learn to get this to work properly.
As I already stated in row1 in Sheet2 I have a raw report output which contains certain Product prices.
Each product is presented with three prices: shop, factory and market.
The problem is that I need to find cells that contain this data about product's prices, then to extract only numbers (prices) from those cells without unnecessary characters in those cells.
The cells which contain product's characteristics (all cells are in row 1,Sheet2 as I said) always follow the next pattern:
Example.
Sheet2
cell (1,D) product:{"shop":15.80
cell (1,E) factory:10.40
cell (1,F) market:14.20}
(cells with this characteristics/special characters/brackets are always consecutive cells in row 1 (Sheet2))
I need this price data formated as some sort of data frame/table starting from cell B2 in Sheet1 in following format:
Sheet1
cell (2,B) 15.80
Cell (2,C) 10.40
cell (2,D) 14.20
Extended example
Sheet2 (raw report)
A | B | C | D | E | F | G | H | I | J | K | L | M | |
1 | events:[{"id": | status:"I" | Restriction:2} | product:{"shop":15.80 | factory:10.40 | market:14.20} | starts: | maxTotal:50 | product:{"shop":30.95 | factory:21.77 | market:28.45} | cutoff:" | ... |
2 |
<tbody>
</tbody>
Sheet1 (after macro or formula run)
A | B | C | D | |
1 | ||||
2 | 15.80 | 10.40 | 14.20 | |
3 | 30.95 | 21.77 | 28.45 | |
4 | .... | .... | .... | |
5 |
<tbody>
</tbody>
Last edited: