I want to align the numerical values of different bacterial isolates like:
1: 250, 274, 281, 296, 300, 305, 310, 317, 325, 337
2: 251, 273, 280, 299, 305, 311, 324, 338, 346
3: 249, 280, 301, 305, 316, 326, 337, 347
The closely related values in a row (std. dev. ±1)
250, 251, 249
274, 273,
281, 280, 280
296
300, 299, 301
305, 305, 305
310, 311,
317, , 316
325, 324, 326
337, 338, 337
, 346, 347
I think in that case, you can use the "paste special" option. Copy your values, and in a new sheet, chose this option. You will have to mark "Transpose" and "Skip empty cells" or something similar, depending on what software you use.
I think in that case, you can use the "paste special" option. Copy your values, and in a new sheet, chose this option. You will have to mark "Transpose" and "Skip empty cells" or something similar, depending on what software you use.
Hello, this operation is quite complicated and needs to be done by writing a macro if you want it to be done automatically. If you are not familiar with macro codes, you will have to do it manually. As Nicolas suggested, you first need to paste your data in rows (TRANSPOSE). Then, you insert cells where values do not match the two other columns. Using conditionnal formatting(home tab, middle-right) can help make it more visual for sorting. I joined a capture of these 3 steps already done (from left to right).
On my own I have always found it best (flexible, historical data manipulation/tracking ability and duplicate record elimination) to pre-format, etc., Excel spreadsheets in an Access or other relational database FIRST. If the spreadsheet is already established, export or copy-paste the data to an Access Table, structure your Table accordingly (with primary key or a composite primary key for a strong unique ID; dropdown lists and subdatasheets for added depth, integration, and normalization, etc.), then Query the Table accordingly, perhaps coding Date formulas for length of time parameters (play around/beta test until they work) or nested conditionals, etc., into your selected Query columns. Lastly, open Excel and import your desired Query results, using PivotTable for producing charts and graphs based on that specified queried data. I hope this helps you.
Thanks all for nice and very relevant answers, but, my problem is bit severe as the data set is large. It will consume lot of time and energy for doing this in the way Nicolas and Jean suggested. Nev, I will try the way you described and come back to you again. Thanks all once again for contribution. I am attaching the data file for better understanding of the situation.
Hello again. With your data in hand, I managed to find a way using only Excel formulas. Your data is aligned as you wanted (I hope) in the file attached. Basically, I used the SMALL formula to find every k-est small value in each group to align with classes of +1 increment. Hope this helps.
Ali Omer I used the SMALL formula. I'm sorry, I can't provide the exact formula that I used on Praveen's data as this was done 6 years ago. I suggest that you ask a specific question using your data and you will probably get specific answers regarding your problem.
- Badges
- Science topic
- Similar topics
- Mathematical Sciences
- Statistics
- Statistical Software